2

Is it possible to group by non-existent(sorry if this is not the right term).

For instance, if I have a table such as:

post_id - status

1 - active

2 - suspended

3 - hold

And the possible values for the status table are: active, suspended, hold, deleted

But the status column may or may not have all of those status types at any given time but I would still like to have a status returned with a 0 count even if it is not in there.

Is it possible to do something such as(pseudo-query):

SELECT COUNT(post_id), status(active, suspended, hold, deleted) FROM users GROUP BY status

P.s. Before anyone suggests to not do the table this way, I have no option as this is how the properties table is structured for this CMS.

Eric
  • 640
  • 12
  • 32
  • You and do a join on the result of the query with synthesized data. E.g. `select coalesce(r.count, 0) as count, s.status from statuses as s left join {that query} as r on s.status = r.status`, where statuses contains the known set of statuses that are grouped over. – user2246674 Sep 05 '13 at 01:38
  • How would I go about doing that? – Eric Sep 05 '13 at 01:40
  • 1
    Can you illustrate your question with a few rows of sample data and a desired output. Maybe it's just me but I'm getting a hard time trying to understand what it is you really after. – peterm Sep 05 '13 at 01:56
  • Ditto @peterm, abd why is your attempt selecting from the users table? Surely it would be the posts table. Do you want to group by user? Please elaborate. – Bohemian Sep 05 '13 at 02:50
  • Doesn't matter what the table name is, it's a fake table with simple data to simplify the question – Eric Sep 05 '13 at 03:24

2 Answers2

4

Try case statement

SELECT SUM(CASE WHEN status = "active" THEN 1 ELSE 0 END) active_count,
       SUM(CASE WHEN status = "suspended" THEN 1 ELSE 0 END) suspended_count,
       SUM(CASE WHEN status = "hold" THEN 1 ELSE 0 END) hold_count,
       SUM(CASE WHEN status = "deleted" THEN 1 ELSE 0 END) deleted_count
FROM posts;

and this 

SELECT "active", SUM(CASE WHEN status = "active" THEN 1 ELSE 0 END) active_count
FROM posts
UNION
SELECT "suspended", SUM(CASE WHEN status = "suspended" THEN 1 ELSE 0 END) suspended_count
FROM posts
UNION
SELECT "hold", SUM(CASE WHEN status = "hold" THEN 1 ELSE 0 END) hold_count
FROM posts
UNION
SELECT "deleted", SUM(CASE WHEN status = "deleted" THEN 1 ELSE 0 END) deleted_count
FROM posts

better yet

SELECT "active", COUNT(*) active_count
FROM posts WHERE status = "active"
UNION
SELECT "suspended", COUNT(*) suspended_count
FROM posts WHERE status = "suspended"
UNION
SELECT "hold", COUNT(*) hold_count
FROM posts WHERE status = "hold"
UNION
SELECT "deleted", COUNT(*) deleted_count
FROM posts WHERE status = "deleted"

See SQL fiddle here

Praveen Lobo
  • 6,956
  • 2
  • 28
  • 40
0

My query is same but in Oracle. Following do the work.

select * from (
  select status from users
)
pivot (
  count(status) as count
  for status in ('active' as A, 'suspended' as S, 'hold' as H, 'deleted' as D)
);

Credits: Using pivot on multiple columns of an Oracle row

satyamera108
  • 465
  • 1
  • 3
  • 11