CODEWITHSUNDEEP
mysqlsql
Philip E
Philip E

Reputation: 858

SQL query should aggregate data per row instead of multiple rows

I want the total views to be listed per row of survey taken:

CREATE TABLE IF NOT EXISTS `survey` (
      `id` int(6) unsigned NOT NULL,
      `title` varchar(200) NOT NULL,
      `url` varchar(200) NOT NULL,
      PRIMARY KEY (`id`)
    ) DEFAULT CHARSET=utf8;
    INSERT INTO `survey` (`id`, `title`, `url`) VALUES
      ('1', 'ants on the hill', 'https://mypoll.io/xyz'),
      ('2', 'crocs in the swamp', 'https://mypoll.io/xyz'),
      ('3', 'baboon on the loose', 'https://mypoll.io/another-term.');


      CREATE TABLE IF NOT EXISTS `views` (
      `id` int(6) unsigned NOT NULL,
      `poll_id` int(6) unsigned NOT NULL,
      `count` int(6) NOT NULL,
       PRIMARY KEY (`id`),
       FOREIGN KEY (poll_id) REFERENCES survey(id)
    ) DEFAULT CHARSET=utf8;
    INSERT INTO `views` (`id`, `poll_id`, `count`) VALUES
      ('1', 1, 1),
      ('2', 2, 1),
      ('3', 2, 1),
       ('4', 3, 1);

What i currently have:

SELECT s.*, COALESCE(v.totalViews, 0) as totalViews 
          FROM survey s
          LEFT JOIN (SELECT id, poll_id, count(id) AS totalViews
          FROM views GROUP BY id) as v on v.poll_id = s.id

I want the results to eventually look like this

id  title               url                     totalViews
1   ants on the hill    https://mypoll.io/xyz   1
2   crocs in the swamp  https://mypoll.io/xyz   2
3   baboon on the loose https://mypoll.io/another-term. 1

Sample Fiddle: http://sqlfiddle.com/#!9/fb8ede/1

Upvotes: 1

Views: 47

Answers (3)

Carsten Massmann
Carsten Massmann

Reputation: 28196

Shouldn't you rather use sum(count) (instead of count(id)) in your query?

SELECT poll_id,title,url, sum(`count`) totalViews FROM survey s
LEFT JOIN views v ON v.poll_id= s.id
GROUP BY poll_id

Otherwise the actual count in views will not be considered in your result.

See my fiddle for a demo

Upvotes: 0

Joakim Danielson
Joakim Danielson

Reputation: 51955

You need to do a minor adjustment to your sub-query, remove id column from the SELECT since it isn't needed and do GROUP BY on poll_id instead

SELECT poll_id, count(id) AS totalViews
      FROM views GROUP BY poll_id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You are joining and aggregating on the wrong column. You just want poll_id:

SELECT s.*, COALESCE(v.totalViews, 0) as totalViews 
FROM survey s LEFT JOIN
     (SELECT poll_id, count(id) AS totalViews
      FROM views 
      GROUP BY poll_id
     ) v
     ON v.poll_id = s.id;

Here is a SQL Fiddle.

Upvotes: 1

Related Questions