Re: SQL query question

"XaFTek" <xaf_at_tekano.fr> a écrit dans le message news: 1011570f.0107200832.a09af62_at_posting.google.com...
> I have a table like
>
> create table data
> (
> id integer not null, // PK
> idSociety integer not null,
> idPeople integer not null,
> billing integer not null,
> dateDate date not null
> )
>
> I would like to know in one SQL query which is the idPeople, for a
> group of idSociety, who have the more SUM(billing) beetween two date.
>
> The result will be like:
>
> idSociety idPeople SUM(billing)
> --------- -------- ------------
> 10 12 102330
> 12 25 1552665
> 2545 35847 12455
>
> I can't find it.
>
> Thank's in advance

select t1.idsociety, t1.idpeople, t1.sumbilling from (select idsociety, idpeople, sum(billing)

      from data
      where datedate between ... and ...
      group by idsociety, idpeople) t1
where t1.sumbilling = (select max(sum(billing)) from data t2
                       where t2.idsociety = t1.idsociety
                         and datedate between ... and ...
                       group by idpeople)

--
Hope this helps
Michel