Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Need help with a SQL query

Re: Need help with a SQL query

From: Matthias Gresz <GreMa_at_t-online.de>
Date: 1998/04/16
Message-ID: <6h4a48$gu8$2@news01.btx.dtag.de>#1/1

Hi,

try something like:

Select

	proj_id PROJECT,
	decode(status,'F/T/P',total,' ') FTP,
	decode(status,'F/T/T',total,' ') FTT,
	decode(status,'P/T/P',total,' ') PTP,
	decode(status,'P/T/T',total,' ') PTT,	-- add more columns
	decode(status,'XXX',total,' ') TOTAL
FROM 
	(SELECT COUNT(Proj_ID) total, status
		FROM
			your_table
		GROUP BY
			Proj_ID, Status
	UNION
	SELECT COUNT(Proj_ID) total, 'XXX' status
		FROM
			your_table
		GROUP BY
			Proj_ID
	)
			

;

dwarakv_at_hotmail.com schrieb:
>
> Hi,
> I have a table with the following columns
>
> proj_id
> parent_id
> agent_id
> status.
>
> Status can take only values F/T/P, F/T/T, P/T/P, P/T/T, LOA, Term, Train.
>
> I need to write a query which lists all the proj_id, the number of agents on
> that proj, the number who have status = F/T/P, the number who have status =
> F/T/T, the number who have status = P/T/P, the number who have status =
> P/T/T, the number who have status = LOA and the number who have status =
> Term. For e.g if the values in the table were
>
> proj_id parent_id agent_id status
> P101 12345 98765 F/T/P
> P101 12345 98888 F/T/P
> P101 12345 88889 F/T/T
> P102 123456 54321 F/T/P
> P102 123456 44444 P/T/P
> P102 123456 77777 F/T/T
> P103 123444 55555 P/T/P
> P103 123444 66666 F/T/T
>
> I need the query to return
>
> proj_id count count count count count count total
> F/T/P F/T/T P/T/P P/T/T LOA Term
>
> P101 2 1 3
> P102 1 1 1 3
> P103 1 1 2
>
> Can someone please help me with the query?
>
> Thanks
> Dwarak
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading

--

Regards

Matthias Gresz    :-)
Received on Thu Apr 16 1998 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US