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