julux julux - 8 days ago 5
MySQL Question

count and sum differents values per each day

how count differents values per each day ? and the total value for each day? in one request.

key1 | key2 | tdate | tview
1 | 20161123454647 | 2016-11-23 11:11:11 | view1
2 | 20161123454648 | 2016-11-23 11:11:11 | view2
3 | 20161122454649 |2016-11-22 11:11:11 | view2
4 | 20161122454650 |2016-11-22 11:11:11 | view1
5 | 20161122454653 |2016-11-22 11:11:11 | view2
6 | 20161122454661 |2016-11-22 11:11:11 | view2
7 | 20161121454622 |2016-11-21 11:11:11 | view3
8 | 20161121454679 |2016-11-21 11:11:11 | view1
9 | 20161121454684 |2016-11-21 11:11:11 | view3


I found to count the total of all values of tview per day :


SELECT DATE(tdate) Date, COUNT(DISTINCT tview) totalOfViews FROM mytable GROUP BY DATE(tdate)


I have a key (key2) which the concatenation of date and the number of a render because I don't want to hive two same render in the same day.

It's most easy for me when I insert a new render with 'INSERT ON DUPLICATE key2 UPDATE'. I update just the number of view with one request. I don't know 'INSET ON DUPLICATE' <2 differents keys> UPDATE :newview. Interesting question too ;-)

The date is a timestamp in my table.

I use php 7, MySQL and PDO to do statement.

One of interesting output :

day | totaView1 | totalView2 | totalView3 | totalView1+view2 |totalOfViews
2016-11-23 | 1 | 1 | 0 | 2 | 2
2016-11-22 | 1 | 3 | 0 | 4 | 4
2016-11-21 | 1 | 0 | 2 | 1 | 3


After i found to range date of request and compare évolution of number view per day. Example :

Day (currentmonth) | totaView1 | totalView1 (lastmonth) |totalOfViews


Is the "alter table" can do this result ?

Regards

Answer

One possibility is to use conditional aggregation:

SELECT DATE(tdate) AS day,
       SUM(CASE WHEN tview = 'view1' THEN 1 ELSE 0 END) AS totaView1,
       SUM(CASE WHEN tview = 'view2' THEN 1 ELSE 0 END) AS totaView2,
       SUM(CASE WHEN tview = 'view3' THEN 1 ELSE 0 END) AS totaView3,
       SUM(CASE WHEN tview = 'view1' OR tview = 'view2'
                THEN 1 ELSE 0 END) AS totaView1Or2,
       COUNT(*) AS totalOfViews
FROM mytable
GROUP BY DATE(tdate)
Comments