[RESOLVED] SQL Question: Combing Results from Same Table

[bschmidt]

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

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!!

[szlamany]

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

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...

[bschmidt]

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!

[bschmidt]

Thanks again, the syntax you provided works in Informix!