CODEWITHSUNDEEP
mysqlsqlgroup-by
Happy Mittal
Happy Mittal

Reputation: 3747

Group by week day

have this portion of a large table T;

+---------------+------------+
| session_id    | visit_time       |
+---------------+------------+
| 4f89cebc109f9 | 1334431476 |
| 4f89cf283d21c | 1334431528 |
| 4f89cf283d21c | 1334431534 |
| 4f89cf3b350a6 | 1334431547 |
| 4f89cf42ab640 | 1334431554 |
+---------------+------------+

I want to find number of session_id weekday-wise. Session_id is not primary key. So I tried: 

select count(distinct(session_id)) from T group by weekday(from_unixtime(time))

But it won't work because if same session_id has visit_time on two different sundays, then it counts them as 1, although it rightly counts 1 when same session_id has visit_time on same sunday.

The expected thing is : I want to know how many session-ids have visit day as sun, mon etc. If a session_id has visited on two different sundays, then they are counted twice, but if on same sunday, then only 1 count. So how can I do it in Mysql ?

Upvotes: 2

Views: 1372

Answers (1)

John Woo
John Woo

Reputation: 263723

use WEEK instead of WEEKDAY

SELECT  WEEK(FROM_UNIXTIME(time)) WeekNo,
        DATE_FORMAT(FROM_UNIXTIME(time),'%a') WeekName,
        COUNT(DISTINCT(session_id)) 
FROM    T 
GROUP   BY WEEK(FROM_UNIXTIME(time)),
           DATE_FORMAT(FROM_UNIXTIME(time),'%a')

Upvotes: 4

Related Questions