sum on multiplication - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sum on multiplication

Author

Topic

adamantium007
Starting Member

4 Posts

Posted - 2011-03-20 : 19:13:04

Here's my query

Select c.cid, sum(p.qnty*o.price) from customer c, purchase p, Offer o where c.cid = p.cid group by c.cid, p.qnty, o.price

apparently the sum doesn't give me the correct answers.

For example Jim with cid 1 from customer table bought items from:

Purchase table
cid club title qnty price
1 abc a 2 6.00
1 def b 3 5.00
1 ghi c 4 4.00

Jim is also part of 3 different clubs.

The total amount spent should be $43.00. However I am not getting the amount. Do I need to cross-reference club to the club table or what have I done wrong with my query?

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-20 : 20:23:15

If the purchase table has the four columns as you have shown here, you may not even need the customer and Offer tables. Run this and see if you get the right answers?

select
   cid,
   sum(qty*price) as TotalSpent
from
   purchase
group by
   cid

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-21 : 21:38:14

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want helP.

SELECT C.customer_id, SUM(P.purchase_qty * O.unit_price) AS purchase_tot
FROM Customers AS C, Purchases AS P, Offers AS O
WHERE C.customer_id = P.customer_id
GROUP BY C.customer_id, P.purchase_qty, O.unit_price;

Your narrative show a Purchases table that has the unit price, but your query is using the Offers table. Where is this Clubs table and what does it mean for the query?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources