Search code examples
sqlouter-join

SQL merge result set from two select queries and transpose counting

I'm trying to extract data from database with a particular template.

In my database I have the following data:

 System | Type | Number
   A      TypeA   1
   A      TypeB   1
   A      TypeA   1
   B      TypeA   1

I'd like to get something like this:

System | #TypeA | #TypeB
   A       2       1
   B       1       0

Is it possible with only one query?

I tried to do full outer join between two queries without success. I'm curious to know the best solution in terms of performance.

Thank you in advance!

Solution

  • You can do this with aggregation using a conditional case expression

    select system,
  Sum(case when type='TypeA' then 1 else 0 end) "#TypeA",
  Sum(case when type='Typeb' then 1 else 0 end) "#TypeB"
from t
group by system;