SQL conditional grouping and summation

Question

My data is as follows:

|cat |subcat |amount| --------------------- |A |1 |123 | |A |2 |456 | |B |1 |222 | |B |2 |333 | 

In the first case, I need to summarize cat and subcat. Easy:

 SELECT cat, subcat, sum(amount) FROM data GROUP BY cat, subcat 

Further, I have a more complicated requirement, when for some cats the amount should be “pressed” into this subcard. This can be saved in another config table:

 |cat |subcat| ------------- |B |1 | 

This tells me that for all cat='B' lines, the sum should be treated as subcat=1 . Also, where cat='B' AND subcat <> 1 sum should be specified as zero. In other words, I need the result:

 |cat |subcat|amount| |A |1 |123 | |A |2 |456 | |B |1 |555 | |B |2 |0 | 

I can not update the data table. Of course, I can SELECT ... INTO in proc and correct the data there, but I wonder if this can be done in one hit.

I can get close to:

 SELECT data.cat, ISNULL(config.subcat, data.subcat), SUM(amount) FROM data LEFT OUTER JOIN config ON (data.cat = config.cat) GROUP BY data.cat, ISNULL(config.subcat, data.subcat) 

... but does not fulfill my second requirement to show cat:B, subcat:2 as zero.

Is it possible?

I use Sybase IQ 12.5 (i.e. old T-SQL, but has a case , which I suspect might be useful)

Answer 1

Here is what I came up with.

 SELECT cat, subcat, sum(amount) FROM ( SELECT d.cat, d.subcat, CASE WHEN c.subcat <> d.subcat THEN 0 ELSE amount END amount FROM data d LEFT OUTER JOIN config c ON (d.cat = c.cat) UNION SELECT d.cat, ISNULL(c.subcat, d.subcat), amount FROM data d LEFT OUTER JOIN config c ON (d.cat = c.cat) WHERE c.subcat <> d.subcat ) AS data2 GROUP BY cat, subcat 

Given that it uses a join table and that my actual dataset is much larger than the one I gave in the question, I think SELECT ... INTO followed by the update may actually be a more efficient approach !

Answer 2

You will need a Data -> Config -> Data connection to translate B2 to B1, then UNION, to SELECT using the Case statement, and then SUM and GROUP BY easily

 SELECT t.CAT, t.SUBCAT, SUM(t.AMOUNT) AMOUNT FROM ( SELECT d.cat, d.subcat, CASE WHEN c.subcat IS NULL OR c.subcat = d.subcat THEN d.amount ELSE 0 END AS amount FROM data d LEFT JOIN config c ON d.cat = c.cat UNION ALL SELECT d.cat, d.subcat, d2.amount FROM data d INNER JOIN config c ON ( d.cat = c.cat ) INNER JOIN data d2 ON c.cat = d2.cat AND c.subcat <> d2.subcat AND c.subcat = d.subcat ) t GROUP BY cat, subcat ORDER BY cat, subcat​ 

In this data.se question you can see a working example .

Note. I added a third “B” value to check where more than one minimized SubCat is located

Another approach that uses WITH and ROLLUP clauses (which are supported in some versions of Sybase, I don't know which one)

 with g as ( SELECT d.cat, d.subcat, c.subcat config_subcat, sum(amount) amount, GROUPING(c.subcat) subcatgroup FROM data d LEFT JOIN config c ON d.cat = c.cat GROUP BY d.cat, d.subcat, c.subcat with rollup ) SELECT g.cat, g.subcat, case when g.config_subcat is null then g.amount WHEN g.subcat = g.config_subcat THEN g2.amount ELSE 0 end amount FROM g LEFT JOIN g g2 ON g.cat = g2.cat and g2.subcatgroup= 1 and g.subcat is not null and g2.subcat is null WHERE g.subcatgroup= 0​​ 

What can be seen in this data.se request

Answer 3

I'm a little confused by the requirements, but I think this is what you want.

 SELECT d.cat, d.subcat, SUM(CASE WHEN c.subcat IS NULL OR c.subcat = d.subcat THEN d.amount ELSE 0 END) as Amount FROM @Data d LEFT OUTER JOIN @Config c ON (d.cat = c.cat) GROUP BY d.cat, d.subcat ORDER BY d.cat 

An example here is http://data.stackexchange.com/stackoverflow/q/120507/

Let me know if this is not what you are going to do.

Answer 4

I am using tsql and here is my code. It is ugly, but it works. Actually, I like your rather close approach (unless you insist on showing B2 = 0).

 SELECT A.cat, A.subcat, CASE WHEN B.IsConfig = 0 THEN A.amount WHEN B.IsConfig = 1 AND C.cat IS NULL THEN 0 ELSE B.amount END AS amount FROM data A INNER JOIN ( SELECT B1.cat, B1.amount, CASE WHEN C1.cat IS NULL THEN 0 ELSE 1 END AS IsConfig FROM ( SELECT cat, SUM(amount) amount FROM data GROUP BY cat ) B1 LEFT OUTER JOIN config C1 ON B1.cat = C1.cat ) B ON A.cat = B.cat LEFT OUTER JOIN config C ON A.cat = C.cat AND A.subcat = C.subcat 

--- I can not comment on others, so I add my question here ---

Comparing my code with others using Execution Plan, my request cost is 46%. Does this mean that it is more effective? Or it just depends :)

Answer 5

Calculate the SUM(amount) for all the "cat" specified in the "config" in the view, and then match them with your table entries "data":

  SELECT data.cat, data.subcat, CASE WHEN dt.subcat IS NULL -- no "config" entry for cat THEN data.amount WHEN dt.subcat = data.subcat -- "config" for cat and subcat THEN dt.total ELSE 0 -- "config" for cat not subcat END AS amount FROM data LEFT JOIN ( SELECT config.cat, config.subcat, SUM(data.amount) AS total FROM config JOIN data USING (cat) GROUP BY 1, 2 ) dt USING (cat); +-----+--------+--------+ | cat | subcat | amount | +-----+--------+--------+ | A | 1 | 123 | | A | 2 | 456 | | B | 1 | 555 | | B | 2 | 0 | +-----+--------+--------+ 4 rows in set (0.00 sec) 

Answer 6

This is a bit like your solution, but UNION is only used to create a list of categories and subcategories. Then the list is merged with another view that essentially matches the right side of your UNION. Here:

 SELECT s.cat, s.subcat, ISNULL(SUM(d.amount), 0) FROM ( SELECT cat, subcat FROM data UNION SELECT cat, subcat FROM config ) s LEFT JOIN ( SELECT d.cat, subcat = ISNULL(c.subcat, d.subcat), d.amount FROM data d LEFT JOIN config c ON d.cat = c.cat ) d ON s.cat = d.cat AND s.subcat = d.subcat GROUP BY s.cat, s.subcat