Using SQL SUM with multiple conditions

Hi there,

I have the following table-1 and I want to query it to generate data as given in table-2.  I want to tabulate items in table-1 to give a summary of total types in per day, total types of out per day,
Can you please help me with this query syntax.  
Thank you.

Table-1
Number| Date             | Time    | Type      | Duration |
200          |09/12/2015 | 11:20    |        in     |      02.00  |
300          |09/12/2015| 10:15      |        in    |      03:00  |
200          |09/12/2015 | 10:00     |        in     |       02.00 |      
200          |09/12/2015 | 13:20    |        out   |      01.00 |
200          |09/11/2015 | 10:00     |        in     |       02.00 |      
200          |09/11/2015 | 14:20    |        out   |      01.00 |
200          |09/11/2015 | 15:20    |        out   |      01.00 |
200          |09/12/2015 | 09:00     |        in     |       05.00 |      
300          |09/11/2015| 10:15      |        out   |      03:00  |
300          |09/12/2015| 10:15      |        in    |      03:00  |
300          |09/12/2015| 10:15      |        in    |      03:00  |
300          |09/12/2015| 10:15      |        in    |      03:00  |

Number |Date| TotalInPerDay |TotalInDurarionPerDay|TotalOutPerDay|TotalOutDurarionPerDay|
200           |09/11/2015 | 1 | 2.00 | 2|02.00
200           | 09/12/2015| 3 | 9:00 | 1| 1:00
300           | 09/11/2015|0| 0:00| 1| 3:00
300           | 09/12/2015|4| 12:00| 0| 0.00
ambuliAsked:
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.

MlandaTCommented:
use a SUM combined with a CASE like so
with Data (Number, Date, Time, Type, Duration) as (
	select 200, '09/12/2015', '11:20', 'in', 02.00
	union select 300, '09/12/2015', '10:15', 'in', 03.00
	union select 200, '09/12/2015', '10:00', 'in', 02.00
	union select 200, '09/12/2015', '13:20', 'out', 01.00
	union select 200, '09/11/2015', '10:00', 'in', 02.00
	union select 200, '09/11/2015', '14:20', 'out', 01.00
	union select 200, '09/11/2015', '15:20', 'out', 01.00
	union select 200, '09/12/2015', '09:00', 'in', 05.00
	union select 300, '09/11/2015', '10:15', 'out', 03.00
	union select 300, '09/12/2015', '10:15', 'in', 03.00
	union select 300, '09/12/2015', '10:15', 'in', 03.00
	union select 300, '09/12/2015', '10:15', 'in', 03.00
)
select
	Number, Date, 
	sum(case when type='in' then 1 else 0 end) TotalInPerDay,
	sum(case when type='in' then Duration else 0 end) TotalInDurationPerDay,
	sum(case when type='out' then 1 else 0 end) TotalOutPerDay,
	sum(case when type='out' then Duration else 0 end) TotalDurationPerDay
from Data
group by Number, Date

Open in new window

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
Éric MoreauSenior .Net ConsultantCommented:
one issue you will have here is that SQL SUM function is not good at adding time. 15 + 15 + 15 + 15 for SQL = 60 minutes and not 1 hour,
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
There is also some inconsistent data in "duration" - there are dots and colons. What is the basic datatype? If it is a datetime or time, adding them is fine, but strings will not work of course.
0
MlandaTCommented:
@Qlemo.. You are right!  I also noticed the same in the data. I assumed that duration is a decimal value  and replaced the : colons with commas

Making duration a decimal is the easiest way to allow the SUM. It just has to always represent a consistent measure of a time unit... So always hours, or always minutes. Then addition will make sense
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
Query Syntax

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.