Re: Need help with a SQL query

Dwarak:

How about

select proj_id, status, count(agent_id)
from table
group by proj_id, status

If you need the total by proj_id then you could create a view

create view proj_id_vw as (select proj_id, count(*) proj_tot from table group by proj_id)

and then use this view in the query to show totals by proj_id:

select proj_id, status, count(agent_id), proj_tot from table, proj_id_vw
where table.proj_id = proj_id_vw.proj_id group by proj_id, status;

An easier way would be to use a query tool to do this grunt work for you... Good luck.

John

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?
>
> Thanks
> Dwarak
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Wed Apr 15 1998 - 00:00:00 CEST