I have this table and I need the query to show the numbers repeated more than 2 times in which draw.
drawnumber|n1|n2|n3| ------------------------- 2000 |3 |20|18| ------------------------------- 2001 |21|18|11| ------------------------------- 2002 |11|20|18| ------------------------------- 2003 |3 |20|18|
I expect this result
numbers|quantity |Draw | -------------------------------------------------- 3 | 2 | 2000, 2003 -------------------------------------------------| 20 | 3 | 2000, 2002, 2003 | -------------------------------------------------| 18 | 3 | 2000, 2002, 2003 | -------------------------------------------------| 11 | 2 | 2001, 2002 | -------------------------------------------------|
I’m doing this:
select NUMBERS,quantity, Draw FROM( SELECT (" "||n1||"-"||n2||"-"||n3||)AS 'NUMBERS', COUNT(*) quantity GROUP_CONCAT(" "||drawnumber)AS Draw from MyTbl GROUP BY NUMBERS HAVING count(*) > 1
But it doesn’t work.
Thanks in advance.
Advertisement
Answer
You can first transpose your data with a cte
and union
, and then use group by
:
with t(d, n) as ( select drawnumber, n1 from draws union all select drawnumber, n2 from draws union all select drawnumber, n3 from draws ) select n, count(*) quantity, group_concat(d) from t group by n having quantity > 1;