Re: SQL query question

From: m.mueller <m.mueller_at_snafu.de>
Date: Sat, 21 Jul 2001 23:24:18 +0200
Message-ID: <3B59F302.52887BF6@snafu.de>

Hello,
I tried to reproduce your table, query.

create table data_table1 ( id                number(4)    CONSTRAINT
pk_id_data_table1 primary key,
                                             idSociety  number(4)    not
null,
                                             idPeople   number(5)    not
null,
                                             billing    number(10,2) not
null,
                                             datDate    date         not
null
     );

insert into data_table1 values(1, 10, 12, 585.33, '21-JUL-00'  );
insert into data_table1 values(2, 10, 12, 1000.33, '01-JUL-00'  );
insert into data_table1 values(3, 10, 13, 2000.33, '01-JUL-00'  );
insert into data_table1 values(4, 12, 25, 2000.33, '20-JUN-00'  );

insert into data_table1 values(5, 12, 25, 4999585.33, '01-APR-01' ); insert into data_table1 values(6, 2545, 35847, 9989.01, '21-JUL-00');

Table created.

1 row created.
....
/*
My comments...
one of the ugliest queries I ever wrote... just to show that is possible!
With other words: Only for academic purpose. Main problem (as I see it): what is the purpose of this query???!! Do like to sum up data (group them by differrent criteria), then you have to group not only by idSociety. You MUST include EVERY column that is not part of the group function. But casual question remains, what info do you want to get from this query?? By the way: You cannot group only only one column, if you select more than one column.
Theoretic prerequisites (suggestions for further reading): aggregating data using group functions, multiple column subquery with hard coded to string converted data values (major big disadvantage!), conversion function (convert date into string) using to_char()) */

select idSociety, idPeople, sum(billing) from data_table1
where datDate IN(

    SELECT datDate
    FROM data_table1
    WHERE datDate >= to_char('20-JUN-00')

       AND datDate <= to_char('21-JUL-01')     )
having sum(billing) >= 1000.00
group by id, idSociety, idPeople, datDate ;
 IDSOCIETY IDPEOPLE SUM(BILLING)
---------- ---------- ------------

        12         25      2000.33
        10         12      1000.33
        10         13      2000.33
      2545      35847      9989.01
        12         25   4999585.33

Regards
Manuela Mueller Received on Sat Jul 21 2001 - 16:24:18 CDT