SQL distinct or group by

Question

My brain aint functioning atm.. Can you pls help me out here:

Got a table with date, analyseid, groupid

I want to get unique groupid's.. perhaps with a count on same groupid's. Also if there are rows with same groupid the date is also the same.

SELECT     date, analyseid,  COUNT(*) AS 'amount', groupid
FROM         myTable
GROUP BY groupid
ORDER BY date DESC

Thanks in advance

Answer 1

You have to GROUP BY all columns you want to SELECT (except of aggregate functions):

SELECT     date, analyseid,  COUNT(*) AS 'amount', groupid
FROM         myTable
GROUP BY date, analyseid,  groupid
ORDER BY date DESC

If you want only groupid and the respective counts, then remove date, analyseid both from SELECT and from GROUP BY:

SELECT     COUNT(*) AS 'amount', groupid
FROM         myTable
GROUP BY groupid

Answer 2

You have to GROUP BY all columns you want to SELECT in the correct order:

SELECT date, analyseid,  COUNT(*) AS 'amount', groupid
FROM myTable
GROUP BY groupid, analyseid, date
ORDER BY date DESC