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

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)

Open in new window

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)

Open in new window

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.

Open in new window

Can anyone point me in the right direction?
petekipeOwnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

prequel_serverCommented:
if you provide me some sample data I could probably help more but my first guess would be that you're getting the wrong job count after you join the tables b/c the same job number is being repeated due to the one to many relation.  So you use Distinct. e.g COUNT( DISTINCT pk_order) as nbr of Jobs
0
Russell FoxDatabase DeveloperCommented:
You can do the total_hours as a sub-query:
SELECT 
	COUNT(pk_order) AS order_count,
		(SELECT SUM(LineItem.quantity)
		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 = t1.date_scheduled
		)
	AS TOTAL_HOURS,
	datepart(dw, t1.date_scheduled) AS [DayOfWeek]
FROM Order t1
WHERE t1.date_scheduled >= '8/24/2014' 
	AND t1.date_scheduled <= '8/30/2014'
GROUP BY datepart(dw, t1.date_scheduled)

Open in new window

0
PortletPaulfreelancerCommented:
use COUNT(DISTINCT ...)

SELECT
      DATEPART(dw, [Order].date_scheduled) AS DayOfWeek
    , SUM(LineItem.quantity)               AS total_hours
    , COUNT(DISTINCT [Order].pk_order)     AS order_count
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)
;

Open in new window

0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

petekipeOwnerAuthor Commented:
Russell:  Got the following error trying your suggestion:

Msg 8120, Level 16, State 1, Line 10
Column 'Order.date_scheduled' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Paul:  Your suggestion runs without error, however there are no rows output.  There should be six for the date range.
0
HuaMin ChenProblem resolverCommented:
Try

select a.order_count ro_count,b.total_hours,b.DayOfWeek
(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)) a,
(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)) b
where a.DayOfWeek=b.DayOfWeek

Open in new window

0
petekipeOwnerAuthor Commented:
I have a little more info on Russell's solution:

I found that the error in line 10 was caused by specifying t1 as the table name. When I removed that, the query ran. In the process, I also realized that I needed to add a test for 'role' to the WHERE clause, so I did that too. but the output of the query is incorrect -- it's returning the same number of hours for each day. I ran the query for the dates 8/17/2014 through 8/23/2014 and got the following:
order_count	total_hours	DayOfWeek
1		3.50		4
1		3.50		5
1		3.50		6

Open in new window

Day 4 would be Wednesday, 8/20/2014, which is the date of the job I've been testing with. So I ran a simplified query to determine the actual number of hours for each of the three days, hard-coding the date for each day. Here's the query:
SELECT  SUM(quantity),
		datepart(dw, 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  Job.role = '0'
		  AND  Order.date_scheduled = '8/20/2014'
GROUP BY datepart(dw, date_scheduled)

Open in new window

And here is the result for each of the three days:
(No column name)  DayOfWeek
2.50		  4

(No column name)  DayOfWeek


(No column name)  DayOfWeek
1.00		  6

Open in new window

Note that DayOfWeek was not returned for day 5, a day which contains no LineItem rows.

It seems that the query is returning the total hours in the week for each day, instead of the total hours in the day for each day.
0
HuaMin ChenProblem resolverCommented:
Did you try mine?
0
petekipeOwnerAuthor Commented:
HuaMinChen, you just about nailed it! The only problem with your solution is that there are Job rows for which there are no LineItem rows. I need the count of all jobs for each day, as well as the sum of all hours for each day, even though some days have not had hours entered yet. What I effectively want is a LEFT JOIN, but I can't make it work.
0
petekipeOwnerAuthor Commented:
...let me clarify: I need the count of all jobs for each day having jobs. If a day has no jobs, I don't need a row returned.
0
petekipeOwnerAuthor Commented:
...and the total_hours for a day having no hours assigned yet can be NULL or zero, whichever is easiest.
0
petekipeOwnerAuthor Commented:
HOLD ON! I got it. By changing the query as follows, I've it working:
select a.order_count, b.total_hours, a.DayOfWeek FROM
(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)) a
LEFT JOIN
(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)) b
ON a.DayOfWeek=b.DayOfWeek

Open in new window

Thank you VERY much for your help, HuaMinChen.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
petekipeOwnerAuthor Commented:
HuaMinChen's solution was the correct solution, except that it had to be slightly modified in order to return the exact result set I was looking for. My final post contained the amended code that now works.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.