Re: SQL Question

Ketema Harris wrote:

> Hey guys I have a SQL teaser I could use a little assistance with.
>
> Imagine a table like below:
>
> rowid|typeid|personid
> ---------------------------
> 1 3 1
> 2 3 1
> 3 1 2
> 4 1 1
> 5 3 2
>
> my desired output is:
>
> person|# of rows of type 3|# of total rows|% of type 3
> ----------------------------------------------------------------
> 1 2
> 3 66.7
> 2 1
> 2 50
>
> how can I achieve this output with one single query? sub selects are
> fine.
>
> Thanks

select person_id, (select count(*) from table b where typeid = 3 and
b.personid = a.personid) as type3, count(*) as total, type3 / total
from table a
group by person_id, (select count(*) from table b where typeid = 3 and
b.personid = a.personid);

Should do the trick.

And this, slightly strange version, should also do the trick. Possibly
a little faster.

SELECT ev.person_id,
count(*) - count(ev2.person_id),
count(*),
(count(*) - count(ev2.person_id)) / count(*)
FROM per.employment_v ev LEFT JOIN per.employment_v ev2
ON(ev.person_id = ev2.person_id
AND ev.position_category_id = ev2.position_category_id
AND ev2.typeid <> 3)
group by ev.person_id;

Jeff