MySql query with multiple counts in result

Question

I have a tables

create table posts (
   text varchar(20),
   created_at datetime,
   user_id integer
);

create table users (
   id integer,
   name varchar(20),
   important bolean
);

create table categories (
   id integer,
   name varchar(20),
);
create table categories_posts (
   category_id integer,
    post_id integer
);

Want to calculate the number of posts for each category between certain times with results like this

START_TIME_PERIOD, category, count_all, count_important, count_normal
'2013-03-06 23:40', 'dogs',    20,       5,               15
'2013-03-06 23:40', 'cats',    22,       6,               16
'2013-03-06 23:40', 'birds',   24,       7,               17

where the importance is based on the users importance.

To get one count e.g. important

select '2013-03-06 23:40', categories.name, count(*)
from posts, users, categories
where posts.id = categories_posts.post_id
and categories.id = categories_posts.category_id
and posts.user_id = users.id
and users.important = true
and posts.created_at BETWEEN '2013-03-06 23:40' AND  '2013-03-06 23:20'
group by 1, 2

What is the best way to get the three counts in a single result set?

Thanks

SUM(IF(condition),1,0) == pseudocount

Wrikken
– Wrikken

2013-03-06 23:49:52 +00:00
Commented Mar 6, 2013 at 23:49 — Wrikken
– Wrikken, Commented Mar 6, 2013 at 23:49

Wrikken · Accepted Answer · 2013-03-06 23:52:42Z

2

SELECT ..... , count(*), SUM(IF(user.important,1,0)) as count_important, COUNT(*) - SUM(IF(user.important,1,0)) as count_normal

answered Mar 6, 2013 at 23:52

Wrikken

70.8k8 gold badges99 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Richard · Accepted Answer · 2013-03-06 23:51:15Z

1

Rather than using COUNT, you can use SUM combined with CASE, e.g.

SELECT COUNT(*) AS count_all, 
    SUM(CASE WHEN important = 1 THEN 1 ELSE 0 END) AS count_important

answered Mar 6, 2013 at 23:51

Richard

30.7k12 gold badges79 silver badges121 bronze badges

1 Comment

DavidC Over a year ago

Thanks - picked @Wrikken's as correct because it was slightly more terse.

Collectives™ on Stack Overflow

MySql query with multiple counts in result

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related