troubleshooting Question

How to return COUNT and SUM in a single SQL SELECT Statement

Avatar of petekipe
petekipeFlag for United States of America asked on
Microsoft SQL Server 2008
12 Comments2 Solutions231 ViewsLast Modified:
I'm having a hard time coming up with a single SELECT statement that returns the count of jobs scheduled for each day of a range of dates, and the total estimated hours for those dates.  I have three tables; Order, Job, and LineItem.  Order contains the scheduled date.  LineItem contains the estimated hours.  Order is one-to-many with Jobs, and Jobs is one-to-many with LineItem.  The following gets the count of Orders for each day of a date range:
SELECT COUNT(pk_order) AS order_count,
       datepart(dw,date_scheduled) AS DayOfWeek
FROM   Order
WHERE  date_scheduled >= '8/24/2014' 
  AND  date_scheduled <= '8/30/2014'
GROUP  BY datepart(dw,date_scheduled)
What I need to do now is add the sum of estimated_hours contained in the LineItem table for each job included in each day's count.  It seems to me that I need to do a sub-select of the sum of hours for each order (via the job), but I don't know where or how to insert it.  I tried the following to try to get the sum of hours for each day, but it doesn't work:
SELECT SUM(LineItem.quantity) AS total_hours,
              datepart(dw,Order.date_scheduled) AS DayOfWeek
FROM   LineItem
INNER  JOIN Job
   ON  LineItem.fk_job = Job.pk_job
INNER  JOIN Order
   ON  Job.fk_order = Order.pk_order
WHERE  LineItem.type = 'L'
  AND  Order.date_scheduled >= '8/24/2014' 
  AND  Order.date_scheduled <= '8/30/2014'
GROUP  BY datepart(dw,Order.date_scheduled)
What I'm looking for is the following:
ro_count	total_hours	DayOfWeek
3		7.4		2
5		11.6		3
5		13.0		4
etc.
Can anyone point me in the right direction?
ASKER CERTIFIED SOLUTION
petekipe
Owner

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros