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

x
?
Solved

SUM with constraint involving a different field

Posted on 2010-01-04
7
Medium Priority
?
389 Views
Last Modified: 2012-05-08
Hello,

I have a report that I am trying to create that provides a couple different Totals that are derived based on conditions found in another field and can't seem to figure out how I can correctly put them into one query. I also want to be able to group these by the month and year.

The information comes from a timesheet table that has the id, date, hours and category. I want to do a query similar to the following:

SELECT id, Month(date), Year(date),  (SUM(hours) WHERE category=1) as Total1, (SUM(hours) WHERE category=2) as Total2
FROM timesheet
GROUP BY Year(date), Month(date)

Any assistance would be greatly appreciated...
0
Comment
Question by:nbotts
7 Comments
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26177501
select baseTable.id,baseTable.mMonth,baseTable.yYear,sum(baseTable.Total1) as Total1,sum(baseTable.Total2) as Total2
from
(
SELECT id, Month(date) as mMonth, Year(date) as yYear, case category when 1 then SUM(hours) else 0 end as Total1,case category when 2 then SUM(hours) else 0 end as Total2
FROM timesheet
GROUP BY Year(date), Month(date),category
) as baseTable
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 26177509
sorry, I missed something in previous query,
try this one:


select baseTable.id,baseTable.mMonth,baseTable.yYear,sum(baseTable.Total1) as Total1,sum(baseTable.Total2) as Total2
from
(
SELECT id, Month(date) as mMonth, Year(date) as yYear, case category when 1 then SUM(hours) else 0 end as Total1,case category when 2 then SUM(hours) else 0 end as Total2
FROM timesheet
GROUP BY id,Year(date), Month(date),category
) as baseTable
group by baseTable.id,baseTable.mMonth,baseTable.yYear

Open in new window

0
 
LVL 14

Expert Comment

by:ajitha75
ID: 26177553
SELECT Month(date), Year(date), (Select SUM(hours) from timesheet where Category=1) as Total1, (Select SUM(hours) from timesheet WHERE category=2) as Total2
FROM timesheet
GROUP BY Year(date), Month(date), Category

Thanks
Ajitha
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:Reza Rad
ID: 26177569
I don't understand why  you want to select id too?
do you have multiple records for each id?
if the answer is no, then what do you mean by select id,mont(date),year(date),... ? this will query from one record for each id !
0
 
LVL 41

Expert Comment

by:Sharath
ID: 26177818
try this
select id,Month(date),Year(Date),
       sum(case when category = 1 then Hours else 0 end) as Total1,
       sum(case when category = 2 then Hours else 0 end) as Total2
  from timesheet
 group by id,Month(date),Year(Date)

Open in new window

0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 26177825
If your id is unique for each record, then there is no point grouping records on id also. You can remove id from SELECT and GROUP BY clauses. Let me know if you are looking for something else.
select Month(date),Year(Date),
       sum(case when category = 1 then Hours else 0 end) as Total1,
       sum(case when category = 2 then Hours else 0 end) as Total2
  from timesheet
 group by Month(date),Year(Date)

Open in new window

0
 

Author Closing Comment

by:nbotts
ID: 31672770
Thank you, this worked the best and most clearly.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

829 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