We help IT Professionals succeed at work.

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

petekipe
petekipe asked
on
227 Views
Last Modified: 2014-09-05
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?
Comment
Watch Question

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
Russell FoxDatabase Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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

petekipeOwner

Author

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.
Peter ChanProblem resolver
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
petekipeOwner

Author

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.
Peter ChanProblem resolver

Commented:
Did you try mine?
petekipeOwner

Author

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.
petekipeOwner

Author

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.
petekipeOwner

Author

Commented:
...and the total_hours for a day having no hours assigned yet can be NULL or zero, whichever is easiest.
Owner
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
petekipeOwner

Author

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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.