group by multiple

[bbunny]

I am counting and grouping by two in a mysql query like:

SELECT username, status, COUNT(status)
FROM logs
WHERE created BETWEEN date and date2
GROUP BY username, status

there are four different status values such as: Started, Working, Deleted, Completed
and there are many usernames

From the query above it obviously COUNTS the status but since grouped by status AND username, it returns username multiple times

This is example of what is occurring, for every different status that is COUNTED, the username is duplicated:

John Doe Started - 23
John Doe Working - 12
Sally Mae Started - 18
Sally Mae Working - 63

and it needs to be one DISTINCT username but of course that will not work because of the GROUP BY:

John Doe Started - 23 Working - 12
Sally Mae Started - 18 Working - 63

(yes its all inside a php do...while loop)

Is there a suggested approach or is the one im taking not a good one? Im out of ideas, maybe because its friday!

Thanks for any suggestions. Hopefully it makes sense.

[LifeinAsia]

Not exactly sure what you are trying to accomplish...
If you want to output something like:
John Doe: Started - 23, Working - 12
Sally Mae: Started - 18, Working - 63
most likely you will need to use the query you already have and parse it in PHP.

[ZydoSEO]

Not sure if you can do this in MySQL but I can do basically what I think you're asking for in MS*SQL as follows:

Create a table to test query:

CREATE TABLE tUserStatus (
Username VARCHAR(16) NOT NULL,
Status VARCHAR(16) NOT NULL
)

Populate table with data to prove query:

INSERT INTO tUserStatus (Username, Status) VALUES ('User1', 'Started')
INSERT INTO tUserStatus (Username, Status) VALUES ('User2', 'Started')
INSERT INTO tUserStatus (Username, Status) VALUES ('User1', 'Started')
INSERT INTO tUserStatus (Username, Status) VALUES ('User1', 'Working')
INSERT INTO tUserStatus (Username, Status) VALUES ('User1', 'Completed')
INSERT INTO tUserStatus (Username, Status) VALUES ('User1', 'Working')
INSERT INTO tUserStatus (Username, Status) VALUES ('User1', 'Working')
INSERT INTO tUserStatus (Username, Status) VALUES ('User1', 'Working')
INSERT INTO tUserStatus (Username, Status) VALUES ('User1', 'Working')
INSERT INTO tUserStatus (Username, Status) VALUES ('User1', 'Started')
INSERT INTO tUserStatus (Username, Status) VALUES ('User1', 'Started')
INSERT INTO tUserStatus (Username, Status) VALUES ('User1', 'Completed')
INSERT INTO tUserStatus (Username, Status) VALUES ('User2', 'Started')
INSERT INTO tUserStatus (Username, Status) VALUES ('User2', 'Working')
INSERT INTO tUserStatus (Username, Status) VALUES ('User2', 'Started')
INSERT INTO tUserStatus (Username, Status) VALUES ('User1', 'Started')
INSERT INTO tUserStatus (Username, Status) VALUES ('User2', 'Working')
INSERT INTO tUserStatus (Username, Status) VALUES ('User1', 'Started')
INSERT INTO tUserStatus (Username, Status) VALUES ('User2', 'Working')
INSERT INTO tUserStatus (Username, Status) VALUES ('User2', 'Completed')
INSERT INTO tUserStatus (Username, Status) VALUES ('User2', 'Started')
INSERT INTO tUserStatus (Username, Status) VALUES ('User2', 'Completed')
INSERT INTO tUserStatus (Username, Status) VALUES ('User1', 'Started')
INSERT INTO tUserStatus (Username, Status) VALUES ('User2', 'Completed')
INSERT INTO tUserStatus (Username, Status) VALUES ('User2', 'Working')
INSERT INTO tUserStatus (Username, Status) VALUES ('User2', 'Completed')
INSERT INTO tUserStatus (Username, Status) VALUES ('User1', 'Completed')

This gives you one row per Username/Status combination with the corresponding counts:

SELECT Username, Status, COUNT(*) AS StatusCount
FROM tUserStatus WITH (NOLOCK)
GROUP BY Username, Status
ORDER BY Username, Status

Username Status StatusCount
---------------- ---------------- -----------
User1 Completed 3
User1 Started 7
User1 WIP 5
User2 Completed 4
User2 Started 4
User2 WIP 4

(6 row(s) affected)

This gives you one row per Username with a column for each Status with the corresponding counts:

SELECT DISTINCT Username,
(SELECT COUNT(*) FROM tUserStatus us2 WHERE us2.Username = us1.Username AND Status = 'Started') AS Started,
(SELECT COUNT(*) FROM tUserStatus us2 WHERE us2.Username = us1.Username AND Status = 'Working') AS Working,
(SELECT COUNT(*) FROM tUserStatus us2 WHERE us2.Username = us1.Username AND Status = 'Completed') AS Completed
FROM tUserStatus us1

Username Started Working Completed
---------------- ----------- ----------- -----------
User1 7 5 3
User2 4 4 4

(2 row(s) affected)

[edited by: ZydoSEO at 10:13 pm (utc) on Aug. 23, 2008]