[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-31
12
Medium Priority
?
174 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?
0
Comment
Question by:petekipe
12 Comments
 
LVL 3

Expert Comment

by:prequel_server
ID: 40295607
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
 
LVL 14

Expert Comment

by:Russell Fox
ID: 40295809
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40295872
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:petekipe
ID: 40295979
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
 
LVL 11

Assisted Solution

by:HuaMinChen
HuaMinChen earned 2000 total points
ID: 40296138
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
 

Author Comment

by:petekipe
ID: 40296141
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
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 40296142
Did you try mine?
0
 

Author Comment

by:petekipe
ID: 40296150
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
 

Author Comment

by:petekipe
ID: 40296151
...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
 

Author Comment

by:petekipe
ID: 40296154
...and the total_hours for a day having no hours assigned yet can be NULL or zero, whichever is easiest.
0
 

Accepted Solution

by:
petekipe earned 0 total points
ID: 40296170
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
 

Author Closing Comment

by:petekipe
ID: 40305466
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

826 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question