help with group by ..
Karol Bieniaszewski
i have problem with group by in this scenario
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
................................................................
if i do this in this way
GROUP BY A.DT, B.DT i get mixed date sum
is this posible to do this in sql
or i must create stored procedure for this?
please help
Karol Bieniaszewski
Wayne Niddery (TeamB)
news:485f...@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)
Karol Bieniaszewski
Uzytkownik "Wayne Niddery (TeamB)" <wnid...@chaffaci.on.ca> napisal w
wiadomosci news:485fb1d8$1...@newsgroups.borland.com...
in tables are not the same number of records
and some of thises tables in some month can be empty :/
Karol Bieniasszewski
Karol Bieniaszewski
Użytkownik "Karol Bieniaszewski" <livius...@poczta.onet.pl> napisał w
wiadomości news:48608627$1...@newsgroups.borland.com...
Views can not be used to solve this
becaouse of some empty month in one of the tables
any more concept?
Karol Bieniaszewski
Karol Bieniaszewski
> i solved this with union and Views and some trick with SUM
Karol Bieniaszewski
Karol Bieniaszewski
Karol Bieniaszewski
Wayne Niddery (TeamB)
>
> Views can not be used to solve this
> becaouse of some empty month in one of the tables
When querying the views, try a left join instead of the inner join in my
example query.
Karol Bieniaszewski
becaouse of problem with storing plans
i change view to selectable stored procedure
and all work as expected and performance also is ok :)
Karol Bieniaszewski