Styjun

I have two queries that use SUM() with GROUP BY. Each query should return same number or rows. In this case my SQL in Sybase returns the rows separate instead on the same line. Here is my query:

SELECT type_id, category_id, category_name, type_code, amount, awarded
 FROM (
 SELECT
 type_id, 
 category_id,
 category_name, 
 type_code,
 CASE 
 WHEN category_id = 1 THEN SUM(amount)
 WHEN category_id = 2 THEN SUM(amount)
 WHEN category_id = 3 THEN SUM(amount)
 WHEN category_id = 4 THEN SUM(amount)
 END AS amount,
 0 AS awarded
 FROM Table 1
 GROUP BY category_id, type_id, category_id, type_code
 UNION
 SELECT
 null AS type_id, 
 ga.grant_category_id,
 '' AS category_name, 
 null AS type_code,
 0 AS amount,
 CASE 
 WHEN t2.category_id = 1 THEN SUM(t2.awarded)
 WHEN t2.category_id = 2 THEN SUM(t2.awarded)
 WHEN t2.category_id = 3 THEN SUM(t2.awarded)
 WHEN t2.category_id = 4 THEN SUM(t2.awarded)
 END AS awarded
 FROM Table2 t2
 INNER JOIN Table3 t3
 ON t2.rec_id = t3.rec_id
 GROUP BY t2.category_id
 ) x
 GROUP BY x.category_id

Query result looks like this:

type_id category_id category_name type_code amount awarded
 1 2 TEST 2 REST 51804.00 0.00
 1 4 TEST 4 REST 39398.00 0.00
 1 3 TEST 3 REST 79922.00 0.00
 1 1 TEST 1 REST 70927.00 0.00
 null 1 null null 0.00 96013.00
 null 2 null null 0.00 78759.00
 null 3 null null 0.00 21436.00
 null 4 null null 0.00 74602.00

I would like the output to look like this:

 type_id category_id category_name type_code amount awarded
 1 2 TEST 2 REST 51804.00 96013.00
 1 4 TEST 4 REST 39398.00 78759.00
 1 3 TEST 3 REST 79922.00 21436.00
 1 1 TEST 1 REST 70927.00 74602.00

How to achieve this output? Thank you.

When @Gordon talked about JOINs, he meant make them subqueries and join them. The following presumse that categories may or may not be returned by either query:

SELECT
 Set1.type_id -- Where this is not found in Set1, you specified null in Set2
 ,ISNULL(Set1.category_id, Set2.grant_category_id) AS category_id
 ,ISNULL(Set1.category_name, '') -- Where this is not found in Set1, you specified <emptyString> in Set2
 ,Set1.type_code -- Where this is not found in Set1, you specified null in Set2
 ,ISNULL(Set1.amount, 0) -- Where this is not found in Set1, you specified 0 in Set2
 ,ISNULL(Set2.awarded, 0) -- Where this is not found in Set2, you specified 0 in Set1
 FROM (
 SELECT
 type_id, 
 category_id,
 category_name, 
 type_code,
 SUM(CASE 
 WHEN category_id between 1 and 4 THEN amount
 ELSE 0
 END) AS amount
 FROM Table1
 GROUP BY
 type_id, 
 category_id,
 category_name, 
 type_code,
 ) Set1
 FULL OUTER JOIN ( 
 SELECT
 t2.grant_category_id,
 SUM(CASE 
 WHEN t2.category_id between 1 and 4 THEN t2.awarded
 END) AS awarded
 FROM Table2 t2
 INNER JOIN Table3 t3
 ON t2.rec_id = t3.rec_id
 GROUP BY
 t2.grant_category_id,
 ) Set2
 ON Set2.grant_category_Id = Set1.category_id

Disclaimer: I could not check the syntax on this, so some minor debugging may be required.

Is that what You want?

SELECT type_id, category_id, category_name, type_code, amount, awarded
 FROM (
 SELECT
 t1.type_id, 
 t1.category_id,
 t1.category_name, 
 t1.type_code,
 SUM(t1.amount) amount,
 SUM(t2.awarded) awarded
 FROM Table1 t1
 INNER JOIN Table2 t2
 ON t1.category_id = t2.category_id
 INNER JOIN Table3 t3
 ON t2.rec_id = t3.rec_id
 GROUP BY 
 t1.type_id, 
 t1.category_id,
 t1.category_name, 
 t1.type_code
 ) x

Moreover, in your example, it seems to me that there is an error.
Shouldn't it will be like that? Where key for Table1 and Table2 is category_id

type_id category_id category_name type_code amount awarded

1 2 TEST 2 REST 51804.00 78759.00

1 4 TEST 4 REST 39398.00 74602.00

1 3 TEST 3 REST 79922.00 21436.00

1 1 TEST 1 REST 70927.00 96013.00