T-SQL Help; CASE Statement and GROUP BY

OkPlankton

OkPlankton

Well-Known Member
Joined
May 23, 2016
Messages
256
Reaction score
264
Location
Johannesburg
Hey SQL Guru's :)

I'm stuck on something that may be very simple but can't wrap my head around it.

I have a few columns with a GROUP BY and a simple join. I also have one aggregration column; [No of Employees]
SELECT H.CALL_CENTER AS [Site], H.TEAM_LEAD_NAME AS [Team], ED.GENDER AS [Gender], ED.RACE AS [Race], COUNT(ED.AGENT_ID) AS [No Of Employees] FROM [dbo].[Hierarchy] H JOIN [dbo].[EmployeeDetails] ED ON H.AGENT_ID = ED.AGENT_ID GROUP BY H.CALL_CENTER, H.TEAM_LEAD_NAME, ED.GENDER, ED.RACE

Results:
1.png

Now I need to add an additional aggregrate column; [No of ACTIVE employees]. So I did this with a CASE Statement and the [TERM_DT]; If the termination date is null then the employee is active.

SELECT H.CALL_CENTER AS [Site], H.TEAM_LEAD_NAME AS [Team], ED.GENDER AS [Gender], ED.RACE AS [Race], COUNT(ED.AGENT_ID) AS [No Of Employees], [B]CASE WHEN H.TERM_DT IS NULL THEN COUNT(ED.AGENT_ID) ELSE '' END [/B]AS [No Of ACTIVE Employees] FROM [dbo].[Hierarchy] H JOIN [dbo].[EmployeeDetails] ED ON H.AGENT_ID = ED.AGENT_ID GROUP BY H.CALL_CENTER, H.TEAM_LEAD_NAME, ED.GENDER, ED.RACE

This gave me an error because I have to either enclose the TERM_DT in an aggregate or add it in the GROUP BY:
2.png

I can't enclose the TERM_DT in an aggregate because it will break the logic of finding "Active Employees" So I added the TERM_DT in the GROUP BY
SELECT H.CALL_CENTER AS [Site], H.TEAM_LEAD_NAME AS [Team], ED.GENDER AS [Gender], ED.RACE AS [Race], H.TERM_DT AS [TERMINATION DATE], COUNT(ED.AGENT_ID) AS [No Of Employees], CASE WHEN DATEADD(YEAR,3, H.TERM_DT) IS NULL THEN COUNT(ED.AGENT_ID) ELSE '' END AS [No Of ACTIVE Employees] FROM [dbo].[Hierarchy] H JOIN [dbo].[EmployeeDetails] ED ON H.AGENT_ID = ED.AGENT_ID GROUP BY H.CALL_CENTER, H.TEAM_LEAD_NAME, ED.GENDER, ED.RACE, [B]H.TERM_DT[/B]

I get almost what I want. It show's the active employees but now it's grouped by date, I only want to group by the initial four groups; Site, Team, Gender, Race - NB: I know I can remove it from the select but it will still be grouped by date. If we look at rows 1 and 2, It's supposed to be; 3 Females with Race A in Team Jimson Bill in one row.
3.png

Any help or some resources would be really appreciated!
 
Look at CTEs where you can cleanly construct each part (no of employees, active employees, etc) and then join those on the columns you need. Or join nested selects, but CTEs makes the sql clean and very readable and maintanable.
with
(
Select sql
) as employeecount,
(
Select sql
) as activeemployeecount

select *
from employeecount ec
lect outer join activeemployeecount aec on aec.xx = ec.xx and.....

what makes itcleaner is the fact you can test each sql and plug them into each cte and use the syntax to easily combine
 
OkPlankton said:
Hey SQL Guru's :)

I'm stuck on something that may be very simple but can't wrap my head around it.

I have a few columns with a GROUP BY and a simple join. I also have one aggregration column; [No of Employees]
SELECT H.CALL_CENTER AS [Site], H.TEAM_LEAD_NAME AS [Team], ED.GENDER AS [Gender], ED.RACE AS [Race], COUNT(ED.AGENT_ID) AS [No Of Employees] FROM [dbo].[Hierarchy] H JOIN [dbo].[EmployeeDetails] ED ON H.AGENT_ID = ED.AGENT_ID GROUP BY H.CALL_CENTER, H.TEAM_LEAD_NAME, ED.GENDER, ED.RACE

Results:
View attachment 1315628

Now I need to add an additional aggregrate column; [No of ACTIVE employees]. So I did this with a CASE Statement and the [TERM_DT]; If the termination date is null then the employee is active.

SELECT H.CALL_CENTER AS [Site], H.TEAM_LEAD_NAME AS [Team], ED.GENDER AS [Gender], ED.RACE AS [Race], COUNT(ED.AGENT_ID) AS [No Of Employees], [B]CASE WHEN H.TERM_DT IS NULL THEN COUNT(ED.AGENT_ID) ELSE '' END [/B]AS [No Of ACTIVE Employees] FROM [dbo].[Hierarchy] H JOIN [dbo].[EmployeeDetails] ED ON H.AGENT_ID = ED.AGENT_ID GROUP BY H.CALL_CENTER, H.TEAM_LEAD_NAME, ED.GENDER, ED.RACE

This gave me an error because I have to either enclose the TERM_DT in an aggregate or add it in the GROUP BY:
View attachment 1315670

I can't enclose the TERM_DT in an aggregate because it will break the logic of finding "Active Employees" So I added the TERM_DT in the GROUP BY
SELECT H.CALL_CENTER AS [Site], H.TEAM_LEAD_NAME AS [Team], ED.GENDER AS [Gender], ED.RACE AS [Race], H.TERM_DT AS [TERMINATION DATE], COUNT(ED.AGENT_ID) AS [No Of Employees], CASE WHEN DATEADD(YEAR,3, H.TERM_DT) IS NULL THEN COUNT(ED.AGENT_ID) ELSE '' END AS [No Of ACTIVE Employees] FROM [dbo].[Hierarchy] H JOIN [dbo].[EmployeeDetails] ED ON H.AGENT_ID = ED.AGENT_ID GROUP BY H.CALL_CENTER, H.TEAM_LEAD_NAME, ED.GENDER, ED.RACE, [B]H.TERM_DT[/B]

I get almost what I want. It show's the active employees but now it's grouped by date, I only want to group by the initial four groups; Site, Team, Gender, Race - NB: I know I can remove it from the select but it will still be grouped by date. If we look at rows 1 and 2, It's supposed to be; 3 Females with Race A in Team Jimson Bill in one row.
View attachment 1315672

Any help or some resources would be really appreciated!
Click to expand...

Cover the last working bit in brackets as a select statement and group from there?

Haven't used SQL this in depth for a while but as Spacerst mentioned a CTE will resolve this as well
 
Wrap your case statement with a sum() then just return 1 if the condition is true and 0 if its not true
 
Cte is cleaner, ugly and dirty is adding the case test to your group by, ie group by DATEADD(YEAR,3, H.TERM_DT) plus others.
 
You can also perform aggregate functions without GROUP BY by using a windowing function. In your case you could do something like this:

Code: 
COUNT(ED.AGENT_ID) OVER(PARTITION BY H.CALL_CENTER)

docs.microsoft.com

OVER Clause (Transact-SQL) - SQL Server

Transact-SQL reference for the OVER clause, which defines a user-specified set of rows within a query result set.
docs.microsoft.com

I would also consider either NULL or 0 in your else statement here instead of '' as your count returns INT

CASE WHEN H.TERM_DT IS NULL THEN COUNT(ED.AGENT_ID) ELSE '' END [/B]AS [No Of ACTIVE Employees]
 
Thanks everyone!

I ultimately wrapped my CASE statement in a SUM like @retromodcoza suggested and it worked perfect. (I'm sure I tried this but anyway it's resolved after hours of puzzling) Phew!

I did also get successful outcomes with @prOd suggestion using the OVER and partition by.

I will try using a CTE as well.

I've learned a lot from this thread!
 
OkPlankton said:
Thanks everyone!

I ultimately wrapped my CASE statement in a SUM like @retromodcoza suggested and it worked perfect. (I'm sure I tried this but anyway it's resolved after hours of puzzling) Phew!

I did also get successful outcomes with @prOd suggestion using the OVER and partition by.

I will try using a CTE as well.

I've learned a lot from this thread!
Click to expand...
Cool, just leave that formulae alone. :ROFL:

Lol Ninja edit.

1653580982841.png
 
Last edited:
Hi guys. I'm hoping someone with a bit more know how of database design can help me with a small question on storing poll data.

So it is similar to the mybroadband poll. You have for example 2 checkboxes: Like, Dislike and the 3rd piece of data is when the user has selected nothing so Abstained.

How could I go about storing this in SQL? I was thinking with a RatingType table with the 3 options and then link that to the User table with a foreign key.

Any feedback welcome!
 
SQL: 
-- Poll table
CREATE TABLE poll (
  poll_id INTEGER PRIMARY KEY,
  poll_name VARCHAR(255) NOT NULL
);

-- Poll options table
CREATE TABLE poll_option (
  option_id INTEGER PRIMARY KEY,
  poll_id INTEGER NOT NULL,
  option_name VARCHAR(255) NOT NULL,
  FOREIGN KEY (poll_id) REFERENCES poll (poll_id)
);

-- User responses table
CREATE TABLE user_response (
  response_id INTEGER PRIMARY KEY,
  poll_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  option_id INTEGER NOT NULL,
  response_timestamp DATETIME NOT NULL,
  FOREIGN KEY (poll_id) REFERENCES poll (poll_id),
  FOREIGN KEY (option_id) REFERENCES poll_option (option_id)
);


ChatGPT says this will work..

This design allows you to store multiple poll options for each poll, and allows users to respond to a poll multiple times. You can also use the response_timestamp column to keep track of when the user submitted their response.
You can then use SQL queries to retrieve and analyze the poll data, such as counting the number of responses for each poll option, or calculating the average response time for a poll.
I hope this helps! Let me know if you have any other questions.
Click to expand...
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X