Sounds like someones trying to pick their lotto numbers...
The answer is yes (to both questions)...
--=======================================================================
-- some test data...IF OBJECT_ID('tempdb..#WinningNumbers', 'U') IS NOT NULL
DROP TABLE #WinningNumbers;
CREATE TABLE #WinningNumbers (
DrawDate DATE NOT NULL,
Num_1 INT NOT NULL,
Num_2 INT NOT NULL,
Num_3 INT NOT NULL,
Num_4 INT NOT NULL,
Num_5 INT NOT NULL,
Num_6 INT NOT NULL
);
WITH
cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
cte_Tally (n) AS (
SELECT TOP 1000
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
cte_n3 a CROSS JOIN cte_n3 b
),
cte_GetNumbers AS (
SELECT
t.n,
Position = DENSE_RANK() OVER (PARTITION BY t.n ORDER BY n.Number),
n.Number
FROM
cte_Tally t
CROSS APPLY ( VALUES -- generate 10 numbers to increase the likelyhood of getting 6 unique numbers.
(ABS(CHECKSUM(NEWID()) % 45) + 1),
(ABS(CHECKSUM(NEWID()) % 45) + 1),
(ABS(CHECKSUM(NEWID()) % 45) + 1),
(ABS(CHECKSUM(NEWID()) % 45) + 1),
(ABS(CHECKSUM(NEWID()) % 45) + 1),
(ABS(CHECKSUM(NEWID()) % 45) + 1),
(ABS(CHECKSUM(NEWID()) % 45) + 1),
(ABS(CHECKSUM(NEWID()) % 45) + 1),
(ABS(CHECKSUM(NEWID()) % 45) + 1),
(ABS(CHECKSUM(NEWID()) % 45) + 1)
) n (Number)
)
INSERT #WinningNumbers (DrawDate, Num_1, Num_2, Num_3, Num_4, Num_5, Num_6)
SELECT
DrawDate = DATEADD(wk, gn.n, '2000-01-03'),
Num_1 = MAX(CASE WHEN gn.Position = 1 THEN gn.Number END),
Num_2 = MAX(CASE WHEN gn.Position = 2 THEN gn.Number END),
Num_3 = MAX(CASE WHEN gn.Position = 3 THEN gn.Number END),
Num_4 = MAX(CASE WHEN gn.Position = 4 THEN gn.Number END),
Num_5 = MAX(CASE WHEN gn.Position = 5 THEN gn.Number END),
Num_6 = MAX(CASE WHEN gn.Position = 6 THEN gn.Number END)
FROM
cte_GetNumbers gn
GROUP BY
gn.n;
-- SELECT * FROM #WinningNumbers wn;
--=======================================================================
-- the solution...
SELECT
n.Number,
NumOfOccurances = COUNT(1)
FROM
#WinningNumbers wn
CROSS APPLY ( VALUES (wn.Num_1), (wn.Num_2), (wn.Num_3), (wn.Num_4), (wn.Num_5), (wn.Num_6) ) n (Number)
--WHERE
-- wn.DrawDate >= '2015-01-01'
-- AND wn.DrawDate < '2016-01-01'
GROUP BY
n.Number
ORDER BY
COUNT(1) DESC;
So, does this mean you'll cut me in is this helps you pick a winner?