Count is displaying one only row despite multiple rows in database

Question

Here is my SQL query:

SELECT 
    COUNT(CASE WHEN  `urgency`='1' THEN 1 END) AS verylow,
    COUNT(CASE WHEN  `urgency`='2' THEN 1 END) AS low,
    COUNT(CASE WHEN  `urgency`='3' THEN 1 END) AS standard,
    COUNT(CASE WHEN  `urgency`='4' THEN 1 END) AS high,
    COUNT(CASE WHEN  `urgency`='5' THEN 1 END) AS critical,
    tbl_users.userName
FROM 
    notes, tbl_users 
WHERE 
    notes.responsible = tbl_users.userID
    AND project_id = '4413' 
    AND (notes.status = 'Ongoing' OR notes.status = 'Not started')

and the output is:

verylow   low   standard   high   critical    userName
5          1       2         1        1        Nick

However this is wrong because i have multiple users in the database who have assigned tasks. and it looks like this in my database:

urgency userName
3       Nick
5       Nick
4       Nick
3       James
1       James
1       Nick
2       Nick
1       James
1       Nick
1       Nick

Any idea why it doesn't count the urgency for the other user and how many different urgencies he has?

Possible duplicate of [Error related to only\_full\_group\_by when executing a query in MySql](https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql) — philipxy, Aug 05 '18 at 18:22

steffen · Accepted Answer · 2018-08-05T17:57:11.127

What you are doing is not entirely correct. If you would turn on MySQL mode ONLY_FULL_GROUP_BY, you'd get a warning, because you are selecting a column that is not in the GROUP BY clause without applying an aggregation function. So you need a GROUP BY clause.

The entire query should look like so:

SELECT 
    COUNT(CASE WHEN  `urgency`='1' THEN 1 END) AS verylow,
    COUNT(CASE WHEN  `urgency`='2' THEN 1 END) AS low,
    COUNT(CASE WHEN  `urgency`='3' THEN 1 END) AS standard,
    COUNT(CASE WHEN  `urgency`='4' THEN 1 END) AS high,
    COUNT(CASE WHEN  `urgency`='5' THEN 1 END) AS critical,
    tbl_users.userName
FROM 
    notes, tbl_users 
WHERE 
    notes.responsible = tbl_users.userID
    AND project_id = '4413' 
    AND (notes.status = 'Ongoing' OR notes.status = 'Not started')
GROUP BY tbl_users.userName;

Can not believe i've didn't test that! Thanks! – Mensur Aug 05 '18 at 17:55 — Mensur, Aug 05 '18 at 17:55

score 0 · Answer 2 · answered Aug 05 '18 at 17:56

0

With COUNT you aggregate your rows. As there is no GROUP BY clause, you aggregate them to one row (rather than, say a row per user).

You are selecting userName. Which? As you select only one row, the DBMS picks one arbitrarily.

answered Aug 05 '18 at 17:56

Thorsten Kettner

89,309
7
49
73

Count is displaying one only row despite multiple rows in database

2 Answers2