Help with SQL statement - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Help with SQL statement

Author

Topic

ProEdge
Yak Posting Veteran

64 Posts

Posted - 2005-11-10 : 23:17:02

I have the following two SQL statements:

SELECT Customer_ID, Count(Customer_ID) As [Number of Purchases]
FROM Orders
Group By Customer_ID;


SELECT Order_Number, Sum(Quantity * Price) As [Purchase Total]
FROM Order_Detail
Group By Order_Number;

Both of these execute correctly but now I'm trying to combine both of them to be able to return 1)Customer_ID, 2)[Number of Purchases], 3)Order_number and 4)[Purchase Total]. I tried combining them like this:

SELECT Customer_ID, o.Order_Number, Count(Customer_ID) As [Number of Purchases], Sum(Quantity * Price) As [Purchase Total]
FROM Orders o, Order_Detail od
Where o.Order_Number = od.Order_Number
Group By Customer_ID, o.Order_Number;

The results, however, are not correct so this is what I need help on. Any help is appreciated. Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-10 : 23:32:45

2 mistake spoted here

First of all your 1st query Count(Customer_ID) does not gives you Number of Purchases. As you are grouping it by Customer_ID, the Count(Customer_ID) will be 1 all the way

It should be Count(Order_Number)

On your combined query, join Orders with Order_Details, the Count(Order_Number) As [Number of Purchases] will give u the count of number of record of Order_Details for a Customer_ID. It should be Count(distinct Order_Number).

Complete queries follows :

SELECT Customer_ID, o.Order_Number, Count(ditinct Order_Number) As [Number of Purchases], Sum(Quantity * Price) As [Purchase Total]
FROM Orders o, Order_Detail od
Where o.Order_Number = od.Order_Number
Group By Customer_ID, o.Order_Number

[KH]

ProEdge
Yak Posting Veteran

64 Posts

Posted - 2005-11-10 : 23:48:48

I'm trying your query but I'm getting an error that reads:
Syntax error (missing operator) in query expression 'Count(DISTINCT Order_Number)'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-11 : 00:06:29

Post table structures, some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

ProEdge
Yak Posting Veteran

64 Posts

Posted - 2005-11-11 : 00:06:58

I searched around and found a few posts in other forums. Most of them say that Access doesn't support the Count Distinct function. And yes, I am using Access and not SQL Server for this.

ProEdge
Yak Posting Veteran

64 Posts

Posted - 2005-11-11 : 00:31:36

I found that if I use a subquery like this:

Select Count(Customer_ID) As [Number of Purchases]
From (Select Distinct Customer_ID From Orders_Copy)

it returns it correctly. Now how can I merge this with your SQL khtan? By the way, I have to count the Customer_ID and not Order_ID.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-11 : 01:16:53

Then post this question at Access Forum

Madhivanan

Failing to plan is Planning to fail

ProEdge
Yak Posting Veteran

64 Posts

Posted - 2005-11-11 : 12:49:18

It doesn't matter that I'm using Access. It's still a SQL issue. The only difference is that it can't be done using Count(Distinct). Another method just needs to be used.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources