Is combining (result sets) of "two queries with aggregate and group by" possible ?

Question

1

Select id,count(*) as totalX FROM my_table WHERE x_factor = 1 GROUP BY id

ResultSet:

 id        totalX
 --------- --------------
         9             34
        10              6
        11             21
        12              3  

2

Select id,count(*) as totalY FROM my_table WHERE y_factor = 1 GROUP BY id

ResultSet 2:

 id        totalY
 --------- --------------
         9             334
        10              56
        11             251
        12              93 

Is there a way I can achieve this:

 id        totalX        totalY         
 --------- --------------  --------------  
         9             34              334 
        10              6               56 
        11             21              251 
        12              3               93 

I would like the solution for Sybase 12.5 on RHEL 5, also I would like to know if this is possible in any other database system.

---thanks for the answer(s)--

Comparing EXECUTION TIME: (For a certain query) 
 Query 1:
Execution Time 61.
SQL Server cpu time: 6100 ms.  SQL Server elapsed time: 12133 ms.

Query 2:
Execution Time 53.
SQL Server cpu time: 5300 ms.  SQL Server elapsed time: 12090 ms.

Query X(1+2):
Execution Time 84.
SQL Server cpu time: 8400 ms.  SQL Server elapsed time: 21456 ms.

Answer 1

By using a CASE/WHEN for the column and summing a 1 or 0 based on true/false, you can get both in the same query... Additionally, you could do the same thing if you wanted the sum of some value in another column... just substitute that for the true value instead of 1.

select 
      id,
      sum( CASE WHEN x_factor = 1 THEN 1 ELSE 0 END ) as X_Count, 
      sum( CASE WHEN y_factor = 1 THEN 1 ELSE 0 END ) as Y_Count
  from
      yourTable
  group by
      id

Answer 2

This should work:

SELECT id, 
       sum(case when x_factor = 1 then 1 else 0 end) as totalX,
       sum(case when y_factor = 1 then 1 else 0 end) as totalY
    FROM my_table 
    WHERE x_factor = 1 
        OR y_factor = 1
    GROUP BY id

Answer 3

The CASE ... {0|1} is a nice trick for people to know, but I think the issue may be simpler than that. Have you tried:

SELECT id, COUNT(x_factor) AS count_x, COUNT(y_factor) AS count_y FROM my_table GROUP BY id