Re: Selecting records using aggregate functions

A nice SQL mind twister. I would try this:

select * from table_name where Person in

• outer view (select person from (
  • inner view select person, sum(sales) from table_name group by person having sum(sales) > 50 ) )

You are creating 2 temporary views indeed: tho inner one lists persons with sales over 50, the outer takes only the column person to serve as filter condition for the "select *" clause

intrepid26_at_my-deja.com schrieb:

> Hi,
> I don't know if this is possible or not, but what
> I am trying to do is select records from a view
> where the sum of values for a selected column is
> greater than a certain value, when grouped by
> another column. For instance, I have the
> following data in my table/view:
>
> Date Person Sales
> --------- -------------------
> 01-JAN-00 Bob 20
> 08-JAN-00 Bob 7
> 15-JAN-00 Bob 15
> 01-JAN-00 Bill 24
> 08-JAN-00 Bill 14
> 15-JAN-00 Bill 15
>
> Now just say from the above table I wanted to
> select all the records for a person where their
> sales totalled 50 or more (This would return all
> of Bill's records because his sales 24+14+15 = 53
> (>50)). Could someone please tell me if this is
> possible?
>
> Thanks in advance,
> Shane
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sun Apr 30 2000 - 00:00:00 CDT