|
|
Posted by --CELKO-- on 05/03/07 18:44
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
>> I'm new to SQL and am having trouble figuring this one out. <<
Your problem is that your schema is a mess. Get any book on RDBMS and
learn about Normalization. You have done some attribute splitting;
two tables with the same structure should be one table. You are
probably mimicking a file system.
CREATE TABLE Foobar
(posting_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
something_nbr INTEGER NOT NULL
CHECK (something_nbr BETWEEN 1 AND 18), --repeated group
something_amt INTEGER NOT NULL,
something_type CHAR(1) CHAR(1) NOT NULL
CHECK something_type IN ('A', 'B')), -- split attribute!
PRIMARY KEY (posting_date, something_nbr, something_type));
This will get you totals for the 1 to 18 categories or whatever they
are:
SELECT posting_date,
SUM(CASE WHEN something_nbr = 1
THEN something_amt ELSE 0 END) AS
tot_01,
..
SUM(CASE WHEN something_nbr = 18
THEN something_amt ELSE 0 END) AS
tot_18
FROM Foobar
GROUP BY posting_date;
you do not let us see it.
>> I'm new to SQL and am having trouble figuring this one out. <<
Your problem is that your schema is a mess. Get any book on RDBMS and
learn about Normalization. You have done some attribute splitting;
two tables with the same structure should be one table. You are
probably mimicking a file system.
CREATE TABLE Foobar
(posting_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
something_nbr INTEGER NOT NULL
CHECK (something_nbr BETWEEN 1 AND 18), --repeated group
something_amt INTEGER NOT NULL,
something_type CHAR(1) CHAR(1) NOT NULL
CHECK something_type IN ('A', 'B')), -- split attribute!
PRIMARY KEY (posting_date, something_nbr, something_type));
This will get you totals for the 'A' and 'B' types
SELECT posting_date,
SUM(CASE WHEN something_type = 'A;
THEN something_amt ELSE 0 END) AS
tot_A,
SUM(CASE WHEN something_type = 'B'
THEN something_amt ELSE 0 END) AS
tot_B
FROM Foobar
GROUP BY posting_date;
You can combine and them as you wish into another SELECT.
>
> I have 2 tables 'A_Totals' & 'B_Totals' each table is identical in
> construction, each column is labeled Date,Total1,Total2,Total3 up to
> Total 18.
>
> I have written some sctipts that populates these tables everyday.
>
> To get a total e.g of Total3 I have used the following SQL statement
>
> For A_Totals:
> SELECT sum(total3) FROM A_Totals
> This returns 18596
>
> For B_Totals
> SELECT sum(total3) FROM B_Totals
> This returns 21794
>
> I would like to create just 1 SQL statement to return both totals so
> I
> tried
> SELECT sum(A_Totals.total3),sum(B_Totals.total3) FROM
> A_Totals,B_Totals
> but this returns 1710832.00 ,1961532.00 and I can't figure out
> why :o(
>
> i also would like to create a statement that gives me a combined
> total
> e.g.
> SELECT sum(A_Totals.total3) + sum(B_Totals.total3) FROM
> A_Totals,B_Totals
> this is returning 3672364.00
>
> Thanks in advance
>
> Rich
[Back to original message]
|