cross tab with missing values

[Ronze55]

Hi All,

I am trying to make my query result look like this:

category first second final
Elephant 5 2 7
tigers
bears 3 3

as you can see i have a row "tigers" without any values.

my raw information would look like this

category order value
elephant first 5
elephant second 2
bears first 3

the query i am using looks like this

select category
, count(iif(order = 'first', value, null))
, count(iif(order = 'second', value, null)
, count(value)
from table1
group by category

how can i get the tiger row in my final view?

thanks in advanced for any help you can send my way.
Christina

 

[lespaul]

do you have a table that contains elephant, bears and tigers as values in a field (like a lookup table)?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Ronze55]

I can make one

it would look like this

Key Category
1 elephants
2 tigers
3 bears

thanks
Christina

 

[PHV]

Have a look at LEFT JOIN

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886