Need help with query - 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

Need help with query

Author

Topic

cyp
Starting Member

1 Post

Posted - 2009-05-28 : 16:56:07

Hi!

I am currently working with a CRM application with a SQL database.

These are the current tables:

Table: Customer
- customerID
- firstname
- lastname
- email
- membernumber

Table: Sale
- saleID
- customerID
- membernumber
- wareID
- warequantum

Table: Ware
- wareID
- name
- category
- price

What I really need help with is to find the customers who shop more than the average shopper within a specific ware-category.

Any ideas? :]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-05-28 : 17:28:16

Post what you've tried so far. How do you define "shop more"? total lifetime number of items? total lifetime sale price? Most sale occurances?

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-29 : 14:33:53

[code]
SELECT CustomerID,Category,TotalShop,ShoppingAverage
FROM
(
SELECT c.CustomerID,w.Category,SUM(s.warequantum) AS TotalShop
FROM Customer c
INNER JOIN Sale s
ON c.CustomerID=s.CustomerID
INNER JOIN Ware w
ON w.wareID=s.wareID
GROUP BY c.CustomerID,w.Category
)m
INNER JOIN
(
SELECT w.Category,SUM(warequantum)*1.0/COUNT(DISTINCT customerID) AS ShoppingAverage
FROM Sale s
INNER JOIN Ware w
ON w.wareID=s.wareID
GROUP BY w.Category
)n
ON n.Category=m.Category
WHERE m.TotalShop>n.ShoppingAverage
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources