select count case statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

select count case statement

Author

Topic

kristofour
Starting Member

9 Posts

Posted - 2010-11-13 : 05:53:10

i am trying to write a select statement and i am having difficulty getting started. I am trying to determine students who are "At Risk" basically if they have >= 3 incidents in category behavior, AND >= 2 category academic, AND >= 3 in category attendance. So here we go: Excuse this query i know its way off!

SELECT Category, StudentID, COUNT(Distinct CASE When Category = Behavior)AS R1 AND R1 >=3, COUNT(Distinct CASE When Category = Attendance) AS R2 AND R2 >= 3, GROUP BY Category, StudentID

Sachin.Nand

2937 Posts

Posted - 2010-11-13 : 11:34:16

Try this

SELECT Category, StudentID from table
GROUP BY Category, StudentID
having COUNT(Distinct CASE When Category = Behavior then 1 end)>=3 and COUNT(Distinct CASE When Category..your other conditions

PBUH

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-15 : 11:33:21

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

Here is my guess. I had to fix the data element names; there is no sucht hign as generic, magical "category", etc.

CREATE TABLE Student_Incidents
(student_id CHAR(10) NOT NULL,
incident_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
incident_cat CHAR(3) NOT NULL,
CHECK (incident_cat IN ('BEV','ATT','ACD')),
PRIMARY KEY (student_id, incident_date, incident_cat));

WITH X
AS
(SELECT student_id,
SUM(CASE WHEN incident_cat = 'ATT' THEN 1 ELSE 0 END) AS att_cnt,
SUM(CASE WHEN incident_cat = 'ACD' THEN 1 ELSE 0 END) AS acd_cnt,
SUM(CASE WHEN incident_cat = 'BEV' THEN 1 ELSE 0 END) AS bev_cnt
FROM Student_Incidents)

SELECT X.student_id
FROM X
WHERE att_cnt >= 3
AND acd_cnt >= 2
AND bev_cnt >= 3;

--CELKO--
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

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-11-16 : 17:22:57

In Celko's response, he is missing a GROUP BY in the CTE:

CREATE TABLE Student_Incidents
(student_id CHAR(10) NOT NULL,
incident_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
incident_cat CHAR(3) NOT NULL,
CHECK (incident_cat IN ('BEV','ATT','ACD')),
PRIMARY KEY (student_id, incident_date, incident_cat));

WITH X
AS
(SELECT student_id,
SUM(CASE WHEN incident_cat = 'ATT' THEN 1 ELSE 0 END) AS att_cnt,
SUM(CASE WHEN incident_cat = 'ACD' THEN 1 ELSE 0 END) AS acd_cnt,
SUM(CASE WHEN incident_cat = 'BEV' THEN 1 ELSE 0 END) AS bev_cnt
FROM Student_Incidents
GROUP BY student_id)

SELECT X.student_id
FROM X
WHERE att_cnt >= 3
AND acd_cnt >= 2
AND bev_cnt >= 3;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources