CODEWITHSUNDEEP
mysqlsqlcountdistinctwhere-clause
NaughtySquid
NaughtySquid

Reputation: 2097

get distinct user rows from table while counting a different row

I am trying to count rows from a SQL Table named "reports", sorted by "working" being 0 / 1 / 2.

Currently I have this SQL query which works okay to give me three rows, each with a counter of how many there are that had "working" as either 0 / 1 / 2.

SELECT `working`, COUNT(`working`) AS `total` FROM `reports` 
WHERE `appid` = 379720 
GROUP BY `working` 
ORDER BY `report_id` DESC LIMIT 30

So it currently (correctly as per the SQL) gives me something like:

Working Total
0 12
1 34
2 18

What I want to do though, is have only one row per user counted, which I can't quite wrap my head around. I can't use a distinct select on an "author_id" field as that ends up included and I can't group by it since I need it grouped by the working int.

To be clear: I want the same results display, but only count one per unique "author_id" from each row.

Any pointers?

Upvotes: 0

Views: 42

Answers (1)

GMB
GMB

Reputation: 222432

You seem to want count(distinct):

SELECT `working`, COUNT(DISTINCT author_id) AS `total` 
FROM `reports` 
WHERE `appid` = 379720 
GROUP BY `working` 
ORDER BY `report_id` DESC

Upvotes: 1

Related Questions