combining multiple count queries in sql

Question

i have a user's table with 3 columns that i'm trying to do counts on and then group by created_at date.

the columns: 'created_at', 'answers_count', 'questions_count'

how do i combine these 3 queries into one and have all the counts grouped by the same created_at date?

here're the 3 separate queries:

-- daily new signups
SELECT date_trunc('day', users.created_at) AS signup_date, count(*) AS new_users
FROM users
GROUP BY signup_date
ORDER BY signup_date DESC;

-- new user answers_count by signup date
SELECT date_trunc('day', users.created_at) AS signup_date, count(*) AS answers_count
FROM users
WHERE users.answers_count > 0
GROUP BY signup_date
ORDER BY signup_date DESC;

-- new user questions_count by signup date 
SELECT date_trunc('day', users.created_at) AS signup_date, count(*) AS qs_received
FROM users
WHERE users.questions_count > 0
GROUP BY signup_date
ORDER BY signup_date DESC;

which db you are using ..? mysql or postgresql?

ScaisEdge
– ScaisEdge

2017-02-22 19:40:05 +00:00
Commented Feb 22, 2017 at 19:40 — ScaisEdge
– ScaisEdge, Commented Feb 22, 2017 at 19:40

Ike Walker · Accepted Answer · 2017-02-22 19:42:26Z

4

You should try using SUM() with CASE to accomplish this.

Try something like this:

SELECT date_trunc('day', users.created_at) AS signup_date, 
  count(*) AS new_users,
  sum(case when answers_count > 0 then 1 else 0 end) as answers_count,
  sum(case when questions_count > 0 then 1 else 0 end) as qs_received,
FROM users
GROUP BY signup_date
ORDER BY signup_date DESC;

answered Feb 22, 2017 at 19:42

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

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

Comments

Roman Tkachuk · Accepted Answer · 2017-02-22 20:17:40Z

1

You can try using count with FILTER to do this.

SELECT date_trunc('day', users.created_at) AS signup_date, 
  count(*) AS new_users,
  count(*) FILTER (WHERE answers_count > 0) as answers_count,
  count(*) FILTER (WHERE questions_count > 0) as qs_received,
FROM users
GROUP BY signup_date
ORDER BY signup_date DESC;

answered Feb 22, 2017 at 20:17

Roman Tkachuk

3,2761 gold badge18 silver badges16 bronze badges

Collectives™ on Stack Overflow

combining multiple count queries in sql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related