Home » SQL & PL/SQL » SQL & PL/SQL » Problems with PIVOT type query
Problems with PIVOT type query [message #221883] Wed, 28 February 2007 15:16 Go to next message
SaipanMan95
Messages: 1
Registered: February 2007
Junior Member
Hi All,

I have been trying this for a couple of days and I have to warn you that I am fairly new in thinking conceptually in SQL but not to programming in general. OK...here's the problem...

I am trying to create a pivot like query which counts a list of ssns on contract dates with 3 different contract qualities of 2, 3 or 4 and a Total representing the total contracts for that particular date dimension period. The contract types(qualities) and totals need to be the column headings. The Contract date dimension reprsents the row values and of course the count(ssn) identifies the numbers for each contract category by date dimension - the facts.

Here is the sql so far....

SELECT AL2.MONTH,
	(SELECT COUNT(AL1.SSN) FROM DW.TABLE_1 AL1
	 WHERE AL1.RECORD=2 ) AS "2",
	(SELECT COUNT(AL1.SSN) FROM DW.TABLE_1 AL1
	 WHERE AL1.RECORD=3 ) AS "3",
	(SELECT COUNT(AL1.SSN) FROM DW.TABLE_1 AL1
	 WHERE AL1.RECORD=4 ) AS "4"
  FROM DW.TABLE_1 AL1, DW.TIME_PERIOD_DIM AL2
WHERE (AL2.CALENDAR_DATE=AL1.CONDATE)
GROUP BY AL2.MONTH
ORDER BY 1;


Now, when I run this query instead of seeing the facts, I get sum totals for each category:

MONTH 2 3 4
---------- ---------- ---------- ----------
200608 16391 397213 71777
200609 16391 397213 71777
200610 16391 397213 71777
200611 16391 397213 71777
200612 16391 397213 71777
200701 16391 397213 71777
200702 16391 397213 71777
200703 16391 397213 71777
200704 16391 397213 71777
200705 16391 397213 71777

I need more detail at each month but the query seems to calculate totals.

(Example of what is sought:)
MONTH 2 3 4
---------- ---------- ---------- ----------
200608 56 5789 34
200609 91 7213 77


Like I had said, I am fairly new to this but also fairly sure the answer I need can be got at by modifying the above select statement.

I would GREATLY appreciate any help and pointing me in the right direction.

Thanks in advance!
Re: Problems with PIVOT type query [message #221909 is a reply to message #221883] Thu, 01 March 2007 00:10 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Does this help?
SELECT AL2.MONTH,
       COUNT(DECODE(AL1.RECORD, 2, AL1.SSN, NULL)) AS "2",
       COUNT(DECODE(AL1.RECORD, 3, AL1.SSN, NULL)) AS "3",
       COUNT(DECODE(AL1.RECORD, 4, AL1.SSN, NULL)) AS "4"
  FROM DW.TABLE_1 AL1, DW.TIME_PERIOD_DIM AL2
WHERE (AL2.CALENDAR_DATE=AL1.CONDATE)
GROUP BY AL2.MONTH
ORDER BY 1;



Ross Leishman
Previous Topic: Tablespace and tables
Next Topic: Select Day of the Week
Goto Forum:
  


Current Time: Sat Dec 03 20:35:14 CST 2016

Total time taken to generate the page: 0.04560 seconds