Re: Need help with a SQL query

From: kiel <kiel_at_webpre.com>
Date: 1998/04/15
Message-ID: <353504DD.22E3@webpre.com>#1/1

dwarakv_at_hotmail.com wrote:
>
> 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?
>

Try DECODE

select proj_id,

	sum(DECODE(status,'F/T/P',1)) "FTP",
        <repeat for each status value>
	count(*)

from tablename
group by proj_id

Christian Received on Wed Apr 15 1998 - 00:00:00 CDT