Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Need help with a SQL query
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