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
 GROUP By - How to add COUNT

Author  Topic 

dwdwone
Yak Posting Veteran

71 Posts

Posted - 2013-04-19 : 19:07:01
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

549 Posts

Posted - 2013-04-19 : 20:25:31
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

71 Posts

Posted - 2013-04-20 : 11:56:03
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

71 Posts

Posted - 2013-04-20 : 12:14:41
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

549 Posts

Posted - 2013-04-20 : 21:04:50
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

71 Posts

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-04-30 : 14:09:38
try
COUNT (DISTINCT F.Description)


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

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-30 : 14:30:36
You can use COALESCE() function to make null values zero and then use count() like this:
[CODE]

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

[/CODE]
Go to Top of Page

erikhaselhofer
Starting Member

30 Posts

Posted - 2013-05-01 : 02:47:43
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

71 Posts

Posted - 2013-05-01 : 13:30:00
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
   

- Advertisement -