Group by when joining the same table twice

Question

I'm writing a query to summarize some data. I have a flag in the table that is basically boolean, so I need some sums and counts based on one value of it, and then the same thing for the other value, like so:

select
   location
  ,count(*)
  ,sum(duration)
from my.table
where type = 'X'
  and location = @location
  and date(some_tstamp) = @date
group by location

And then the same for another value of the type column. If I join this table twice, how do I still group so I can only get aggregation for each table, i.e. count(a.*) instead of count(*)...

Would it be better to write two separate queries?

EDIT

Thanks everybody, but that's not what I meant. I need to get a summary where type = 'X' and a summary where type = 'Y' separately...let me post a better example. What I meant was a query like this:

select
   a.location
  ,count(a.*)
  ,sum(a.duration)
  ,count(b.*)
  ,sum(b.duration)
from my.table a, my.table b
where a.type = 'X'
  and a.location = @location
  and date(a.some_tstamp) = @date
  and b.location = @location
  and date(b.some_tstamp) = @date
  and b.type = 'Y'
group by a.location

What do I need to group by? Also, DB2 doesn't like count(a.*), it's a syntax error.

Answer 1

Your example with the join doesn't make a lot of sense. You're doing a Cartesian product between A and B. Is this really what you want?

The following will find count(*) and sum(duration) for each pair that satisfies the WHERE clause. Based on your description, this sounds like what you're looking for:

select
   type
  ,location
  ,count(*)
  ,sum(duration)
from my.table
where type IN ('X', 'Y')
  and location = @location
  and date(some_tstamp) = @date
group by type, location

Answer 2

select
   location
  ,Sum(case when type = 'X' then 1 else 0 end) as xCount
  ,Sum(case when type = 'Y' then 1 else 0 end) as YCount
  ,Sum(case when type = 'X' then duration else 0 end) as xCountDuration
  ,Sum(case when type = 'Y' then duration else 0 end) as YCountDuration
from my.table
where 
location = @location
  and date(some_tstamp) = @date
group by location

This should work in SQL Server. I guess db2 should have something similar.

Edit: Add a where condition to limit the records to select type = X or type = Y, if "type" can have value other than X and Y.

Answer 3

To make the counts work, instead of count(a.*) just do count(a.location), or any other not-null column (the PK would be ideal).

As to the main question, either of the answers given by shahkalpesh or George Eadon above would work. There is no reason in this example to join the table twice.