SQL Question

[Michael Colbert]

What makes sales and ups records unique ? Seems like your join condition
need to include date as well as store.
--
Michael Colbert mcol...@bugfree.com
Intelligent Computer Solutions, Inc. http://www.bugfree.com

Alpha <al...@msn.com> wrote in article
<01bc5f87$f4e05ee0$cd952299@default>...
> I have two tables:
>
> ups.dbf sales.dbf
> date D8 store C2 guests N5 date D8 store C2 amount N5
>
> I want my query to sum guests in the ups table and sales amounts in the
> sales table for each day:
>
> sample data
> ups sales
> 1/1/96 AA 5 1/1/96 AA 100.00
> 1/1/96 AA 5 1/1/96 AA 100.00
> 1/1/96 BB 10 1/1/96 BB 200.00
> 1/1/96 BB 10 1/1/96 BB 200.00
>
> The cursor should contain:
>
> 1/1/96 AA 10 200.00
> 1/1/96 BB 20 400.00
>
> All my attempts at linking ups and sales have the effect of doubling
> everything, like:
>
> SELECT ups.date, ups.store, sum(ups.guests), sum(sales.amount) ;
> FROM ups, store WHERE ups.date=datevariable and sales.date=ups.date ;
> GROUP BY store INTO CURSOR temp
>
> The SQL seems to be running the summation process twice. If I put 3
> records in each table, the summations are triple what they should be.
>
> Could someone explain the way of doing what I want, and why what I've
done
> doesn't work?
>
> Alpha
>
>
>
>
>
>
>

[Anders Altberg]

>> SELECT ups.date, ups.store, sum(ups.guests), sum(sales.amount) ;
FROM ups, store WHERE ups.date=datevariable and sales.date=ups.date ;
GROUP BY store INTO CURSOR temp

The SQL seems to be running the summation process twice. If I put 3
records in each table, the summations are triple what they should be. <<

Alpha,
That's the way the ball bounces when you sum and join.

select u.store, u.date, ;
sum(u.guests)/sqrt(count(*)), ;
sum(s.amount)/sqrt(count(*)) ;
from ups as U join sales as s ;
on u.store=s.store and u.date=s.date ;
group by u.store, u.udate

you can add WHERE u.date=somedate to restrict it to a particular date.

You wouldn't have this problem if salesamount and guests were in the
same table or if you first summed each table by itself and finally
joined the query cursors without further summing.

select store, date , sum(guests) as totalGuests from ups ;
group by 1,2 into cursor q1
select store, date , sum(saels) AS TotalSales from Sales ;
group by 1,2 into cursor q2
select u.store, u.date, u.totalguests, s.totalsales ;
from q1 as U join q2 as S ;
on u.date = s.date and u.store = s.store ;
into cursor daystat
You can store all three queries as database views and just USE daystat
to run all there queries.

-Anders

-Anders

--
Anders Altberg Anders....@swipnet.se
05/16/97 14:08
---------
Using: OUI PRO 1.5.0.2 from http://www.dvorak.com