SUM and Group using IN clause

Question

Post reply

SUM and Group using IN clause

rjjh78

SSCrazy

Points: 2787
More actions
November 3, 2016 at 2:53 pm

#315212

Hi, I am looking for a way to create a query to SUM and Group BY by using IN clause similiar to below.
Is this possible or is another method recommended?
SELECT
CID,
SUM(Hours) WHERE CID IN mycodes,
FROM MyClrTbl
WHERE MyCodes IN ('Full', 'part', 'Contract')
GROUP BY ID, MyCodes
Basically, I want to end up with a table that shows:
ID Full Part Contract and the values for each underneath each row.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Unless you are not explaining some critical detail, how about this:

SELECT CID,

MyCodes,

SUM(Hours)

FROM MyClrTbl

WHERE MyCodes IN ('Full', 'part', 'Contract')

GROUP BY CID, MyCodes;

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

drew.allen SSC Guru Points: 76988 More actions · Answer 2

Assuming that the obvious solution isn't what you were looking for, try the following.

SELECT

CID,

MyCodes,

SUM(CASE WHEN MyCodes IN ('Full', 'part', 'Contract') THEN Hours END),

FROM MyClrTbl

GROUP BY ID, MyCodes

Although, it looks like you might be trying to define MyCodes on the fly as the set consisting of ('Full', 'part', 'Contract'). If that's case, options include a CTE, derived table, or CROSS APPLY.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

rjjh78 SSCrazy Points: 2787 More actions · Answer 3

Thank you for those who replied. I think I did a bad job explaining what I wanted to do. Below I have provided sample code and 2 queries. The 2 queries produce the results I would like to see, but I would like to combine the 2 queries into one (if this is possible). This way I would be able to have 1 query to show Sick time and all other "time" summed together.

CREATE TABLE #getData (CID int, Hrs int, Hr_Type varchar(20))

INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (10,8, 'Full')

INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (10,8, 'Sick')

INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (10,8, 'Full')

INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (20,8, 'Sick')

INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (20,8, 'Part')

INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (25,4, 'Contract')

INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (30,4, 'Part')

-- QRY 1:

SELECT CID, SUM(Hrs) Hrs_Worked FROM #getData WHERE Hr_Type IN ('Full', 'Part') GROUP BY CID

--QRY 2:

SELECT CID, SUM(Hrs) Hrs_Worked FROM #getData WHERE Hr_Type IN ('Sick') GROUP BY CID

-- Desired Results:

CIDHrs_WorkedHRs_Sick

10328

202416

30200

pietlinden SSC Guru Points: 63503 More actions · Answer 4

Does this work? Where's your data for 25?

SELECT CID

,SUM(Worked) AS Hrs_Worked

,SUM(Sick) AS Hrs_Sick

FROM (

SELECT CID

, Hrs

--, Hr_type

, CASE WHEN Hr_Type IN ('Full', 'Part', 'Contract') THEN Hrs ELSE 0 END Worked

, CASE WHEN Hr_Type = 'Sick' THEN Hrs ELSE 0 END Sick

FROM #getData) x

GROUP BY CID;

sgmunson SSC Guru Points: 110640 More actions · Answer 5

rjjh78 (11/7/2016)
Thank you for those who replied. I think I did a bad job explaining what I wanted to do. Below I have provided sample code and 2 queries. The 2 queries produce the results I would like to see, but I would like to combine the 2 queries into one (if this is possible). This way I would be able to have 1 query to show Sick time and all other "time" summed together.
CREATE TABLE #getData (CID int, Hrs int, Hr_Type varchar(20))
INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (10,8, 'Full')
INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (10,8, 'Full')
INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (10,8, 'Full')
INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (10,8, 'Sick')
INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (10,8, 'Full')
INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (20,8, 'Sick')
INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (20,8, 'Sick')
INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (20,8, 'Part')
INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (20,8, 'Part')
INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (20,8, 'Part')
INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (25,4, 'Contract')
INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (25,4, 'Contract')
INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (25,4, 'Contract')
INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (25,4, 'Contract')
INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (25,4, 'Contract')
INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (30,4, 'Part')
INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (30,4, 'Part')
INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (30,4, 'Part')
INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (30,4, 'Part')
INSERT INTO #getData (CID, Hrs, Hr_Type) VALUES (30,4, 'Part')
-- QRY 1:
SELECT CID, SUM(Hrs) Hrs_Worked FROM #getData WHERE Hr_Type IN ('Full', 'Part') GROUP BY CID
--QRY 2:
SELECT CID, SUM(Hrs) Hrs_Worked FROM #getData WHERE Hr_Type IN ('Sick') GROUP BY CID
-- Desired Results:
CIDHrs_WorkedHRs_Sick
10328
202416
30200

Here's a query that gets the results you asked for, but given that this leaves out any hours designated as "Contract", I have to ask if you intend to ignore those?

CREATE TABLE #getData (

CID int,

Hrs int,

Hr_Type varchar(20)

);

INSERT INTO #getData (CID, Hrs, Hr_Type)

VALUES(10, 8, 'Full'),

(10, 8, 'Full'),

(10, 8, 'Sick'),

(10, 8, 'Full'),

(20, 8, 'Sick'),

(20, 8, 'Part'),

(25, 4, 'Contract'),

(30, 4, 'Part'),

(30, 4, 'Part');

-- QRY 1:

SELECT CID,

SUM(CASE WHEN Hr_Type IN ('Full', 'Part') THEN Hrs ELSE 0 END) AS HRs_Worked,

SUM(CASE WHEN Hr_Type = 'Sick' THEN Hrs ELSE 0 END) AS HRs_Sick

FROM #getData

WHERE Hr_Type IN ('Full', 'Part', 'Sick')

GROUP BY CID;

DROP TABLE #getData;

Let us know about the Contract hours...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

rjjh78 SSCrazy Points: 2787 More actions · Answer 6

Thank you guys! I left contract out in error this has been very helpful!!!

Joe Celko SSCertifiable Points: 5749 More actions · Answer 7

>> I am looking for a way to create a query to SUM and GROUP BY by using IN clause [sic: predicate, not clause] similar to below. <<

Why did you fail to post DDL? Did you not read the forum rules? Then why did you invent your own language with non-SQL syntax? Why do not you know the ISO 11179 rules for naming things?

Why do you believe there is such a thing as a generic "_id" in RDBMS? No, it has to be the identifier of something in particular; in your case, it is a new entity called a "C" which makes no sense. The use of the "_tbl" in a table name is a design flaw called a Tibble; we actually have jokes and make fun of people who do this among the more experienced SQL programmers.

Here is an attempt, made without the DDL you were supposed to post, and cleaning up your query:

SELECT c_id, SUM(something_hours) AS something_hours_tot

FROM MyClr -- bad name!

WHERE c_id IN employment_code IN ('Full', 'Part', 'Contract'),

GROUP BY c_id;

>> c_id, full_something_hours_tot, part_something_hours_tot, contract_something_hours_tot.<<

SELECT c_id,

SUM(CASE WHEN employment_code = 'Full'

THEN something_hours ELSE 0.00 END)

AS full_something_hours_tot,

SUM(CASE WHEN employment_code = 'Part'

THEN something_hours ELSE 0.00 END)

AS part_something_hours_tot,

SUM(CASE WHEN employment_code = 'Constract'

THEN something_hours ELSE 0.00 END)

AS contract_something_hours_tot

FROM MyClr

GROUP BY c_id;

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

MMartin1 One Orange Chip Points: 27795 More actions · Answer 8

Assuming it is the 'Sick' hours that you are mainly focusing on , the following is as simple as I can code it:

selectcid,

sum(case when hr_type = 'sick' then 0 else hrs end) as Hrs_Worked,

sum(case when hr_type = 'sick' then hrs else 0 end) as Hrs_Sick,

sum(hrs) as Hrs_Total

from#getData

groupby cid

----------------------------------------------------