🚀 go-pugleaf

"Karol Bieniaszewski" <liviuslivius@poczta.onet.pl> wrote in message 
news:485f842b@newsgroups.borland.com...
>
> sumary by day in one row per day
>
> tableA
> ID Integer Primary key
> DT DATE NOT NULL,
> QU NUMERIC(18, 4),
> PRICE NUMERIC(18, 2)
>
> tableB
> ID Integer Primary key
> DT DATE NOT NULL,
> QU NUMERIC(18, 4),
> PRICE NUMERIC(18, 2)
>
> i need some like
> select
> {A.DT or B.DT}, sum(A.QU), SUM(A.QU * A.PRICE), SUM(B.QU), SUM(B.QU * 
> A.PRICE)
> FROM
> tableA
> tableB
>
> GROUP BY {A.DT or B.DT ... ????}
>
> result should by like
>
> DT                 SUMA       WARTA    SUMB   WARTB
> 2008-01-01   19               590            14          890
> 2008-01-02   12               789             11         654

Maybe I'm not understanding what you want, but I think this should be doable 
with an inner join linked on the date

select a.dt, sum(a.qu), sum(a.qu * a.price), sum(b.qu), sum(b.qu * b.price)
from tablea a
inner join tableb b on b.dt = a.dt
group by a.dt

However, this assumes there is the same number of records in both tables for 
a given date. If there are different numbers of records then this will give 
you wrong totals. In that case you would need to use subqueries which will 
probably not perform well.

You could create a view on each table that gets the correct sums for that 
table by date, and then you can do the above query on those views to get 
correct totals.

-- 
Wayne Niddery - TeamB (www.teamb.com)
Winwright, Inc. (www.winwright.ca) 

Uzytkownik "Wayne Niddery (TeamB)" <wniddery@chaffaci.on.ca> napisal w 
wiadomosci news:485fb1d8$1@newsgroups.borland.com...
> "Karol Bieniaszewski" <liviuslivius@poczta.onet.pl> wrote in message 
> news:485f842b@newsgroups.borland.com...
>>
>> sumary by day in one row per day
>>
>> tableA
>> ID Integer Primary key
>> DT DATE NOT NULL,
>> QU NUMERIC(18, 4),
>> PRICE NUMERIC(18, 2)
>>
>> tableB
>> ID Integer Primary key
>> DT DATE NOT NULL,
>> QU NUMERIC(18, 4),
>> PRICE NUMERIC(18, 2)
>>
>> i need some like
>> select
>> {A.DT or B.DT}, sum(A.QU), SUM(A.QU * A.PRICE), SUM(B.QU), SUM(B.QU * 
>> A.PRICE)
>> FROM
>> tableA
>> tableB
>>
>> GROUP BY {A.DT or B.DT ... ????}
>>
>> result should by like
>>
>> DT                 SUMA       WARTA    SUMB   WARTB
>> 2008-01-01   19               590            14          890
>> 2008-01-02   12               789             11         654
>
> Maybe I'm not understanding what you want, but I think this should be 
> doable with an inner join linked on the date
>
> select a.dt, sum(a.qu), sum(a.qu * a.price), sum(b.qu), sum(b.qu * 
> b.price)
> from tablea a
> inner join tableb b on b.dt = a.dt
> group by a.dt
>
> However, this assumes there is the same number of records in both tables 
> for a given date. If there are different numbers of records then this will 
> give you wrong totals. In that case you would need to use subqueries which 
> will probably not perform well.
>
> You could create a view on each table that gets the correct sums for that 
> table by date, and then you can do the above query on those views to get 
> correct totals.
>
thanks Wayne i'll try views for this
in tables are not the same number of records
and some of thises tables in some month can be empty :/

Karol Bieniasszewski