MySQL - select count(*), group by, but maintain separation

Question

I've got a table recording views of programs. Each program can have two different types (1=video, 2=audio)

My table to record the views:

TABLE views
view_id
user_agent
created_at
type
program_id (which related to table programs)

My current MySQL call:

SELECT COUNT(*) as views, 
       `mp`.`title`, 
       `mp`.`program_date` as date 
FROM (`applications_media_views` as v) 
JOIN `media_programs` as mp ON `v`.`program_id` = `mp`.`program_id` 
GROUP BY `v`.`program_id` 
ORDER BY `views` desc

The trouble is, this does not keep the types separated. I need to report on views by program_id, but I need to keep the types separate.

What am I missing?

Answer 1

Do you need to "group by program_id, type" and select type in your query?

If not, can you show an example query output that works like you want?

Answer 2

You need to add v.type to both the SELECT and GROUP BY clauses:

SELECT COUNT(*) as views, 
       `mp`.`title`, 
       `mp`.`program_date` as date,
       v.type 
FROM (`applications_media_views` as v) 
JOIN `media_programs` as mp ON `v`.`program_id` = `mp`.`program_id` 
GROUP BY `v`.`program_id`, v.type 
ORDER BY `views` desc

You'll get an output row for each program/type combination.

Answer 3

You can group by more than one thing. Try this:

GROUP BY `v`.`program_id`, `v`.`type`

Answer 4

You should add v.type to your group by clause, and you should also add v.type to your select clause so you can see which set is from which program type.

Answer 5

Answers above are all well. But it seems to me, that you should also put "title" and "program_date" in your group by clause, cause they are not present in aggregate function.