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
   ON  LineItem.fk_job = Job.pk_job
   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
Can anyone point me in the right direction?

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