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.
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.OrderIDGROUP BY F.Description, C.LName, C.CustIdORDER BY Fleet, Total DESCYes, 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,TotalQueens,Queens County Hospital,34000,50,5199.25Queens Flushing Memorial,76000,25,2776.16Richmond,Richmond General,54000,18,1724.30Sorry 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:SELECTF.Description AS Fleet, C.Lname as Customer, C.Custid, COUNT(F.Description) as TotalOrders,SUM(P.Price) as TotalFROMtblOrder as OINNER JOIN tblcustomer as C on C.Custid = O.CustidINNER JOIN tblOrderDrivers as OD ON OD.OrderID = O.OrderIDINNER JOIN tblEmployees AS E ON E.EmployeeID = OD.DriverIDINNER JOIN tblFleets AS F ON F.FleetID = E.FleetIDINNER JOIN tblOrderItems AS P ON P.OrderID = OD.OrderIDGROUP BY F.Description, C.LName, C.CustIdORDER BY Fleet, Total DESC |
|
|
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? |
|
|
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. |
|
|
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. |
|
|
dwdwone
Yak Posting Veteran
71 Posts |
Posted - 2013-04-30 : 14:02:41
|
How would one approach this sticky problem? Maybe as a UNION? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-04-30 : 14:09:38
|
tryCOUNT (DISTINCT F.Description) Too old to Rock'n'Roll too young to die. |
|
|
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(SELECTF.Description AS Fleet, C.Lname as Customer, C.Custid, COALESCE(F.Description, 0) AS Description, SUM(P.Price) as TotalFROMtblOrder as OINNER JOIN tblcustomer as C on C.Custid = O.CustidINNER JOIN tblOrderDrivers as OD ON OD.OrderID = O.OrderIDINNER JOIN tblEmployees AS E ON E.EmployeeID = OD.DriverIDINNER JOIN tblFleets AS F ON F.FleetID = E.FleetIDINNER JOIN tblOrderItems AS P ON P.OrderID = OD.OrderID ) AS T GROUP BY Description, LName, ustIdORDER BY Fleet, Total DESC[/CODE] |
|
|
erikhaselhofer
Starting Member
30 Posts |
Posted - 2013-05-01 : 02:47:43
|
quote: Originally posted by dwdwoneSELECT 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.OrderIDGROUP BY F.Description, C.LName, C.CustIdORDER 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. |
|
|
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. |
|
|
|
|
|
|
|