mysql daily count grouped by one column

Question

I have the following:

SELECT COUNT(*),DAYNAME(sip_Date) AS DAY,sip_callid  FROM request_line WHERE DAYNAME(sip_Date) = 'Saturday' GROUP BY sip_callid;

with an output:

+----------+----------+-------------------------------------+
| COUNT(*) | DAY      | sip_callid                          |
+----------+----------+-------------------------------------+
|        6 | Saturday | 133E840E-F6A411DE-AA5C9CC8-17D97B68 |
|        6 | Saturday | 162BF117-F6A611DE-A986A3C8-FB0ED04D |
|       18 | Saturday | 163C78CD-F6A611DE-9FC6D5B8-87CB70FC |
|       12 | Saturday | 1A7C93B6-F6A711DE-91F4BA52-B031BEF4 |
|        6 | Saturday | 1AB2BC9C-F6A311DE-A972A3C8-FB0ED04D |
|        4 | Saturday | 1F7E3D6C-F6AC11DE-A045D5B8-87CB70FC |
|        4 | Saturday | 1FA68517-F6AC11DE-A048D5B8-87CB70FC |
|        4 | Saturday | 1FCECD5A-F6AC11DE-A04AD5B8-87CB70FC |
|        4 | Saturday | 1FEDED7C-F6AC11DE-A04DD5B8-87CB70FC |
|        6 | Saturday | 2625EFE-F6A311DE-A96FA3C8-FB0ED04D  |
+----------+----------+-------------------------------------+

But I need a count of each unique "sip_callid" per day, so I need an ouput like:

+----------+----------+
| COUNT(*) | DAY      |
+----------+----------+
|        70 | Saturday |

But I can't seem to do it with a group by on sip_callid. If I do a group by DAY, then I get all of the rows even if the sip_callid is the same obviously. Any help will be appreciated.

Answer 1

How about using DISTINCT keyword?

Answer 2

I think you would need COUNT(DISTINCT)

Something like this

SELECT  DAYNAME(sip_Date) AS DAY,
        COUNT(DISTINCT sip_callid)
FROM    request_line
WHERE DAYNAME(sip_Date) = 'Saturday' 
GROUP BY DAYNAME(sip_Date)