[RESOLVED] SQL Question: Combing Results from Same Table

I have a table with the following data:

cd nm fy fq amount
1 Joe 2007 2 500
1 Joe 2007 2 425
1 Joe 2007 3 500
2 Jane 2007 1 450
2 Jane 2007 2 500
2 Jane 2007 4 200
2 Jane 2007 4 300

Code:

---

I would like to have a query produce the following results:

cd        nm        fy        fq1cnt        amount        fq2cnt        amount        fq3cnt        amount        fq4cnt        amount
1        Joe        2007        0        0        2        925        1        500        0        0
2        Jane        2007        1        450        1        500        0        0        2        500

---

fq is fiscal quarter fq1cnt would be the count of records for fiscal quarter 1 totaling to the amount. Each record in the table is distinguished by another field called BOC which I didn't include in the example.

Can someone point me in the right direction? Thank you!!

You did not say what the database was - I'll assume MS SQL. If it's ACCESS then use the IIF() function instead of the CASE/WHEN statements

Code:

---

Select cd,nm,fy
        ,Sum(Case When fq=1 Then 1 Else 0 End) "fq1cnt"
        ,Sum(Case When fq=1 Then amount else 0 End) "amount"
        ,Sum(Case When fq=2 Then 1 Else 0 End) "fq2cnt"
        ,Sum(Case When fq=2 Then amount else 0 End) "amount"
        ,Sum(Case When fq=3 Then 1 Else 0 End) "fq3cnt"
        ,Sum(Case When fq=3 Then amount else 0 End) "amount"
        ,Sum(Case When fq=4 Then 1 Else 0 End) "fq4cnt"
        ,Sum(Case When fq=4 Then amount else 0 End) "amount"
        group by cd,nm,fy

---

You might need to change "amount" to "amount1", etc - but that depends on the DB...

Thanks for the response. It's Informix SQL, so I'll check and see if this syntax is applicable. If all else fails, I'll write code to create and attach a data array to my report (AR), or create a recordset from a variant array with rds. Sure would be nice to do it in one SQL though!

Thanks again, the syntax you provided works in Informix! :)