MS Access Query For Combining rows

Question

I have a query which returns data in following format:

xxx yyy     count
-------------------------------
a   cat1    23
a   cat2    34
a   cat3    12
b   cat1    34
b   cat2    1
b   cat3    2
c   cat1    34
c   cat2    123
c   cat3    34
d   cat1    34
d   cat2    12
d   cat3    34

I need to modify my query in such a way that i need to categorize two values of yyy column into single and combine their count

xxx yyy         count
-------------------------------
a   cat1        23
a   cat2 & cat3 36
b   cat1        34
b   cat2 & cat3 3
c   cat1        34
c   cat2 & cat3 157
d   cat1        34
d   cat2 & cat3 36

Please suggest if you have any ideas!

See http://stackoverflow.com/questions/92698/combine-rows-in-access-2007/93863#93863 — Fionnuala, May 15 '12 at 12:09
Thanks for quick response Remou!! i need to know if i can do this purely using sql statement.. ?? — user1396054, May 15 '12 at 12:17
I agree with Remou, although technically possible if the "cat" names do not change, you would end up "unioning" your SQL row by row and thus defeat the whole point of the exercise. — Matt Donnan, May 15 '12 at 12:31
yes.. I agree.. Despite trying out all possible ways i could think of, i could acheive this.. I worked out a work around though.. Thanks all.. — user1396054, May 16 '12 at 08:34

score 0 · Answer 1 · answered Jun 03 '14 at 14:07

Assuming you always want cat1 broken out and there are only 2 other cats...

SELECT xxx, yyy, COUNT(*) AS count
FROM table
WHERE yyy = 'cat1'
GROUP BY xxx, yyy
UNION
SELECT xxx, 'cat2 & cat3' AS yyy, COUNT(*) AS count
FROM table
WHERE yyy <> 'cat1'
GROUP BY xxx

If you there additional cats and you only want 2 and 3, change WHERE yyy <> 'cat1' to WHERE yyy IN ('cat2','cat3')

MS Access Query For Combining rows

1 Answers1