combine values in view?

Is there an easy way to do this?
I need to pull sales values for each store and make one record per month.
This used to be easy, as I was provided only one sales record per month per
store, but now I've been told that SOME of the stores have a "secondary" ID
from which to pull sales numbers, and these numbers need to be added to the
number from their PRIMARY number before displaying them. I'm not sure how
to do this.
Here is some sample data (cols don't align well here - sorry):
STORES:
ID1 ID2 STORE
100 AAA A-STORE
200 B-STORE
300 CCC C-STORE
400 D-STORE
(note that B-Store and D-Store have only ONE ID, not a secondary ID)
SALES:
ID SALES MONTH YEAR
100 5.00 01 2004
AAA 5.00 01 2004
200 5.00 01 2004
300 5.00 01 2004
CCC 5.00 01 2004
400 5.00 01 2004
(note that sales values from IDs "100" and "AAA" both belong to "A-Store",
and "300" and "CCC" belong to "C-Store"; also that there is a set of sales
records for each month & year so those fields need to be accounted for.)
I need a view that will show:
ID SALES MONTH YEAR
100 10.00 01 2004
200 5.00 01 2004
300 10.00 01 2004
400 5.00 01 2004
(note that IDs 100 and 300 show the combined sales of both their primary AND
secondary IDs)
Is this easily doable? If yes, how'!!
Thanks!
-RThere are some data integrity problems here, and in particular,
if the ID2 values are not unique, this is something of a mess, but
if the data is not messed up, something like this should work:
select ID, sum(SALES) as SALES, [MONTH], [YEAR]
from (
select ID, SALES, [MONTH], [YEAR]
from SALES
union all
select ST.ID1, SA.SALES, SA.[MONTH], SA.[YEAR]
from SALES AS SA join STORES AS ST
on SA.ID = ST.ID2
) S
group by ID, [MONTH], [YEAR]
If the data is messed up, this query could provide completely
wrong information. You would be better off keeping track
of the store IDs differently:
-- primary IDs only, with store attributes
CREATE TABLE STORES (
ID char(3) primary key,
StoreName varchar(30),
.. other attributes of a store
)
-- all IDs, primary and alternate, for stores, with
-- the primary storeID for each
CREATE TABLE STORE_IDS (
ID char(3) primary key,
storeID char(3) references STORES(ID)
-- put an index on storeID to support the FK
)
The foreign key on SALES would now link to this
second table instead of the first. The query would
be different, too - something like this:
select ST.ID, sum(SA.SALES) as SALES, SA.[MONTH], SA.[YEAR]
from STORES as ST
join STORE_IDS as I
on I.storeID = ST.ID
join SALES as SA
on SA.ID = I.ID
group by ST.ID, SA.[MONTH], SA.[YEAR]
Steve Kass
Drew University
r wrote:

>Is there an easy way to do this?
>I need to pull sales values for each store and make one record per month.
>This used to be easy, as I was provided only one sales record per month per
>store, but now I've been told that SOME of the stores have a "secondary" ID
>from which to pull sales numbers, and these numbers need to be added to the
>number from their PRIMARY number before displaying them. I'm not sure how
>to do this.
>Here is some sample data (cols don't align well here - sorry):
>STORES:
>ID1 ID2 STORE
>100 AAA A-STORE
>200 B-STORE
>300 CCC C-STORE
>400 D-STORE
>(note that B-Store and D-Store have only ONE ID, not a secondary ID)
>SALES:
>ID SALES MONTH YEAR
>100 5.00 01 2004
>AAA 5.00 01 2004
>200 5.00 01 2004
>300 5.00 01 2004
>CCC 5.00 01 2004
>400 5.00 01 2004
>(note that sales values from IDs "100" and "AAA" both belong to "A-Store",
>and "300" and "CCC" belong to "C-Store"; also that there is a set of sales
>records for each month & year so those fields need to be accounted for.)
>I need a view that will show:
>ID SALES MONTH YEAR
>100 10.00 01 2004
>200 5.00 01 2004
>300 10.00 01 2004
>400 5.00 01 2004
>(note that IDs 100 and 300 show the combined sales of both their primary AN
D
>secondary IDs)
>Is this easily doable? If yes, how'!!
>Thanks!
>-R
>
>
>sqlsql