By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,776 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,776 IT Pros & Developers. It's quick & easy.

How to show distinct column values when it doesn't come in query result

P: 61
Hi All,

Expand|Select|Wrap|Line Numbers
  1. select DISTINCT t.ticket_sev, count(t.ticket_id) as ticket_count 
  2. from tickets t
  3. where t.ticket_start > 1338938508 
  4. group by t.TICKET_sev;
  5.  
This yeilds the correct value.

What I get is

Expand|Select|Wrap|Line Numbers
  1.     ticket_sev ticket_count
  2.     11    1
  3.     4    2
  4.     1    6
  5.  
What I need is a count for each ticket_sev even if there is no entry. So ticket_sev go from (1,2,3,4,5,6). So I need something like


Expand|Select|Wrap|Line Numbers
  1.     ticket_count ticket_sev
  2.     11    1
  3.     4    2
  4.     1    6
  5. 0       3
  6. 0       4
  7. 0       5
  8.  
How can I achieve this? I know I can use IFNULL() to put 0 not idea how I can show the 3,4,5 as it doesn't come up in my where clause (for the right reason of course).

Many thanks.
Jun 27 '12 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,366
1) There's no need to use distinct if you're using a group by.
2) Outer Join the query to your table that contains all the unique ticket_sevs.
Jun 27 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.