SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 GROUP By - How to add COUNT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dwdwone
Yak Posting Veteran

USA
68 Posts

Posted - 04/19/2013 :  19:07:01  Show Profile  Reply with Quote
Good afternoon!

I've written a query which displays the total orders by each customer grouped by fleet. The fleet id is located in our employee file.

I am wondering how I can get the query to include the number of orders on each line. I have tried using count (*) but that failed. Here's the working code without the count in it:

SELECT
F.Description AS Fleet,
C.Lname as Customer,
C.Custid,
SUM(P.Price) as Total
FROM
tblOrder as O
INNER JOIN tblcustomer as C on C.Custid = O.Custid
INNER JOIN tblOrderDrivers as OD ON OD.OrderID = O.OrderID
INNER JOIN tblEmployees AS E ON E.EmployeeID = OD.DriverID
INNER JOIN tblFleets AS F ON F.FleetID = E.FleetID
INNER JOIN tblOrderItems AS P ON P.OrderID = OD.OrderID
GROUP BY F.Description, C.LName, C.CustId
ORDER BY Fleet, Total DESC

Yes, it's convoluted but I didn't design the thing! How would I add the number of orders so that the results would look something like this:

Fleet,localname,custid,totalorders,Total
Queens,Queens County Hospital,34000,50,5199.25
Queens Flushing Memorial,76000,25,2776.16
Richmond,Richmond General,54000,18,1724.30

Sorry about the data's appearance. If someone could tell me how to properly visually format it, I would be much obliged.

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/19/2013 :  20:25:31  Show Profile  Reply with Quote
Try this:

SELECT
F.Description AS Fleet, 
C.Lname as Customer, 
C.Custid, 
COUNT(F.Description) as TotalOrders,SUM(P.Price) as Total
FROM
tblOrder as O
INNER JOIN tblcustomer as C on C.Custid = O.Custid
INNER JOIN tblOrderDrivers as OD ON OD.OrderID = O.OrderID
INNER JOIN tblEmployees AS E ON E.EmployeeID = OD.DriverID
INNER JOIN tblFleets AS F ON F.FleetID = E.FleetID
INNER JOIN tblOrderItems AS P ON P.OrderID = OD.OrderID
GROUP BY F.Description, C.LName, C.CustId
ORDER BY Fleet, Total DESC




Go to Top of Page

dwdwone
Yak Posting Veteran

USA
68 Posts

Posted - 04/20/2013 :  11:56:03  Show Profile  Reply with Quote
Too cool! Was it not working because I used count(*), and therefore it didn't "know" which column to count?
Go to Top of Page

dwdwone
Yak Posting Veteran

USA
68 Posts

Posted - 04/20/2013 :  12:14:41  Show Profile  Reply with Quote
Ooops. I spoke to soon.

Because sum(p.price) counts multiple rows off an order's price detail to determine the total charges on each order, COUNT (F.Description) ends up counting the same orders multiple times. So, if an order has 4 line items, it's count is 4.
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/20/2013 :  21:04:50  Show Profile  Reply with Quote
You have to choose the aggregate element you want to count based on your need.
Just make sure that the element you choose does not allow NULL values, because the aggregate functions ignore NULL values, you might end up getting incorrect count.
Go to Top of Page

dwdwone
Yak Posting Veteran

USA
68 Posts

Posted - 04/30/2013 :  14:02:41  Show Profile  Reply with Quote
How would one approach this sticky problem? Maybe as a UNION?
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8768 Posts

Posted - 04/30/2013 :  14:09:38  Show Profile  Visit webfred's Homepage  Reply with Quote
try
COUNT (DISTINCT F.Description)


Too old to Rock'n'Roll too young to die.
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/30/2013 :  14:30:36  Show Profile  Reply with Quote
You can use COALESCE() function to make null values zero and then use count() like this:


SELECT 
 Fleet, 
 Customer, 
 Custid, 
 COUNT(Description) as TotalOrders, SUM(P.Price) as Total
(SELECT
F.Description AS Fleet, 
C.Lname as Customer, 
C.Custid, 
COALESCE(F.Description, 0) AS Description, SUM(P.Price) as Total
FROM
tblOrder as O
INNER JOIN tblcustomer as C on C.Custid = O.Custid
INNER JOIN tblOrderDrivers as OD ON OD.OrderID = O.OrderID
INNER JOIN tblEmployees AS E ON E.EmployeeID = OD.DriverID
INNER JOIN tblFleets AS F ON F.FleetID = E.FleetID
INNER JOIN tblOrderItems AS P ON P.OrderID = OD.OrderID ) AS T 
GROUP BY Description, LName, ustId
ORDER BY Fleet, Total DESC

Go to Top of Page

erikhaselhofer
Starting Member

30 Posts

Posted - 05/01/2013 :  02:47:43  Show Profile  Reply with Quote
quote:
Originally posted by dwdwone
SELECT
F.Description AS Fleet,
C.Lname as Customer,
C.Custid,
SUM(P.Price) as Total,
COUNT(DISTINCT P.OrderID) as orderCount
FROM
tblOrder as O
INNER JOIN tblcustomer as C on C.Custid = O.Custid
INNER JOIN tblOrderDrivers as OD ON OD.OrderID = O.OrderID
INNER JOIN tblEmployees AS E ON E.EmployeeID = OD.DriverID
INNER JOIN tblFleets AS F ON F.FleetID = E.FleetID
INNER JOIN tblOrderItems AS P ON P.OrderID = OD.OrderID
GROUP BY F.Description, C.LName, C.CustId
ORDER BY Fleet, Total DESC


I, think, the red highlighted section will do what you want. Since you used P.Price for the SUM, I figured P. was the way to go. That should also, probably, eliminate any problem with counting nulls, unless you want to count orders with nothing on them.
Go to Top of Page

dwdwone
Yak Posting Veteran

USA
68 Posts

Posted - 05/01/2013 :  13:30:00  Show Profile  Reply with Quote
Awesome. That last solution worked perfectly.

Every day I discover how much I don't know. Hopefully one day all I learn here will coalesce.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000