Aggregate Sum Problem

akc · November 3rd, 2003, 05:09 PM

Hi,

Here is my data:

PosID | Name | Gender | Ethnicity
---------------------------------
828 alan male caucasian
828 jen female black
828 cathy female black
829 david male caucasian
829 peter male caucasian
829 vince male black

Here is the outcome I need

PosID | male/caucasian | female/caucasian | male/black / female/black
828 1 0 0 2
829 2 0 1 0

Any help will be appreciated.
Thanks in advance.

Alan

Jeff Mason · November 3rd, 2003, 05:38 PM

You're try to do a variation on a cross tab (or pivot) query.

It's ugly.

The idea is to create an expression for each output column where you SUM either a 1 or a zero depending on whether the column criteria match for each desired output column:

Code:

SELECT PosID, 
    SUM(CASE WHEN Gender='male' AND Ethnicity='caucasian' THEN 1 ELSE 0 END) as 'male/caucassian',
    SUM(CASE WHEN Gender='female' AND Ethnicity='caucasian' THEN 1 ELSE 0 END) as 'female/caucasian',
...
GROUP BY PosID
ORDER BY PosID;

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

akc · November 4th, 2003, 10:07 AM

Thanks Jeff.

Alan