Intresting SQL 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

Intresting SQL Query

Author

Topic

Nom1fan
Starting Member

4 Posts

Posted - 2010-09-06 : 11:11:13

Hello all, first post :)

Maybe you can help me:

Here is a pic of the tables

What I am trying to do is return the following query: "Return the number of orders for every client by credit card type",

meaning, if for example 'David' has 3 credit cards: 2 'Visa' cards and 1 'MasterCard' card, the answer will contain 1 row of 'Visa' , in which all orders in the 'Visa' cards will be summed,
and another row with the number of orders of the 'MasterCard'.

The code I have written so far is as follows:

SELECT DISTINCT C.cust_name,CC.cust_num,CC.cc_type,COUNT(*)
FROM  online_shop.customer C, online_shop.credit_card CC,
       (SELECT  C.cust_num,CC.cc_type,CC.cc_num
        FROM online_shop.customer C, online_shop.order O, online_shop.credit_card CC
        WHERE C.cust_num=O.cust_num and CC.cc_num=O.cc_num
        GROUP BY CC.cc_num) AS Temp
WHERE C.cust_num=Temp.cust_num AND CC.cc_type=Temp.cc_type
GROUP BY Temp.cc_num

But unfortunately the answer that returns is close to what I need but it seems the count is not working properly.

Maybe you could tell me what's wrong with my query or perhaps suggest a different and better one.

Thanks in advance!

Nom1fan
Starting Member

4 Posts

Posted - 2010-09-06 : 11:14:06

Here is a link to the tables (the image link didn't seem to work)

http://img688.imageshack.us/f/shoptables.

Nom1fan
Starting Member

4 Posts

Posted - 2010-09-06 : 11:17:54

sorry can't edit :(
correct link: http://img688.imageshack.us/f/shoptables.png/

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-09-06 : 11:20:26

SELECT C.cust_name, CC.cust_num, CC.cc_type, COUNT(*)
FROM online_shop.customer C
inner join online_shop.credit_card CC on CC.cust_num = C.cust_num
inner join online_shop.order O on O.cust_num = C.cust_num and O.cc_num = CC.cc_num
GROUP BY C.cust_name, CC.cust_num, CC.cc_type

simpler is better

Nom1fan
Starting Member

4 Posts

Posted - 2010-09-06 : 12:45:18

quote:
Originally posted by AndrewMurphy

SELECT C.cust_name, CC.cust_num, CC.cc_type, COUNT(*)
FROM online_shop.customer C
inner join online_shop.credit_card CC on CC.cust_num = C.cust_num
inner join online_shop.order O on O.cust_num = C.cust_num and O.cc_num = CC.cc_num
GROUP BY C.cust_name, CC.cust_num, CC.cc_type

simpler is better

Thanks man! you're awesome :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources