CODEWITHSUNDEEP
mysql
John
John

Reputation: 147

MySQL count duplicate row values and don't show duplicate row names

I have table STREAMS:

+----+----------+-------------------------------------------------------------+-------------+-------------+
| id | name     | stream                                                      | server      | bouquet     | 
+----+----------+-------------------------------------------------------------+-------------+-------------+
|  1 | HOME     | ["http://ur11.com/", "http://ur12.com/"]                    | ["1", "3"]  | ["3","2"]   |
|  2 | HOME     | ["http://ur15.com/", "http://ur16.com/"]                    | ["12", "13"]  | ["31","12"]   |
+----+----------+-------------------------------------------------------------+-------------+-------------+

I have table WATCHED:

+----+--------+------------+---------+
| id | stream | month      | watched |
+----+--------+------------+---------+
| 16 |      2 | 2017-02-01 |       1 |
|  2 |      1 | 2017-01-01 |       3 |
| 15 |      2 | 2017-01-01 |       4 |
+----+--------+------------+---------+

And need to get this from query:

+--------+------------+---------+
| stream | month      | watched |
+--------+------------+---------+
|      2 | 2017-02-01 |       5 |
|      1 | 2017-01-01 |       3 |
+--------+------------+---------+

i try to use:

SELECT DISTINCT stream, month, watched FROM watched;

this show all ok without duplicated but how to count same stream and show in watched? In example you see that we have stream 2 - 1 watched and stream 2 - 4 watched...i need to plus this together (1 + 4) and show like in table stream 2 - watched 5..but i im getting only first stream from distinct..i try count but it shows me duplicated....do i need group by?

Upvotes: 1

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

This produces the output you want. It this what you are trying to do?

select stream, max(month) as month, sum(watched) as watched
from watched
group by stream;

Upvotes: 2

Related Questions