SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 If then statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cwildeman
Starting Member

USA
40 Posts

Posted - 07/08/2011 :  09:40:39  Show Profile  Reply with Quote
Hi,
I am a novice SQL writer. I have a field called NoInjury which is a bit field. When it is check on the Access form that is used to create the records, it means there is no injury for this record and when it is unchecked it means there is an injury. I want to write a query that counts the number of Injuries and am trying to figure out how to write an If then statement that will assign a No or Unchecked value as a 1 and then sum or permorm a count on the No values. Can someone help?
Chuck

Chuck W

robvolk
Most Valuable Yak

USA
15675 Posts

Posted - 07/08/2011 :  10:10:55  Show Profile  Visit robvolk's Homepage  Reply with Quote
SELECT SUM(CASE WHEN NoInjury=1 THEN 1 END) NoInjuryTotal, SUM(CASE WHEN NoInjury=0 THEN 1 END) InjuryTotal
FROM myTable
Go to Top of Page

cwildeman
Starting Member

USA
40 Posts

Posted - 07/08/2011 :  10:40:54  Show Profile  Reply with Quote
Thanks. That worked. Chuck

Chuck W
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22760 Posts

Posted - 07/08/2011 :  11:01:47  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by robvolk

SELECT SUM(CASE WHEN NoInjury=1 THEN 1 END) NoInjuryTotal, SUM(CASE WHEN NoInjury=0 THEN 1 END) InjuryTotal
FROM myTable


I would add ELSE 0 part in the code to avoid a warning Warning: Null value is eliminated by an aggregate or other SET operation.

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 07/08/2011 :  15:01:16  Show Profile  Visit jcelko's Homepage  Reply with Quote
>> I am a novice SQL writer. <<

Yep, and you have several classic mindset errors.

>> I have a field [sic: columns are not anything like fields] called NoInjury which is a bit[bits was assembly language, not good SQL] field [sic]. When it is check on the ACCESS form [SQL people do not care about the front end] that is used to create the records [sic: rows are not records], it means there is no injury for this record [sic] and when it is unchecked it means there is an injury. <<

The good SQL programmer will have a column with check constraints for the injury codes (There is a US Army system, the ICD codes, etc.) which will include a code for “no injury” in them. Do a GROUP BY on this code and get a count of all the injury codes. Now you would use a CASE expression and not an IF-THEN to filter out the injury/no injury counts:

SELECT SUM(CASE WHEN WHEN injury_code = '0000' THEN ! ELSE 0 END)
AS no-injury_cnt,
SUM(CASE WHEN WHEN injury_code <> '0000' THEN ! ELSE 0 END)
AS injury_cnt
FROM Personnel


--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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15675 Posts

Posted - 07/08/2011 :  15:06:54  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
Originally posted by jcelko

>> I am a novice SQL writer. <<

SELECT SUM(CASE WHEN WHEN injury_code = '0000' THEN ! ELSE 0 END)
AS no-injury_cnt,
SUM(CASE WHEN WHEN injury_code <> '0000' THEN ! ELSE 0 END)
AS injury_cnt
FROM Personnel
How does one SUM() an exclamation mark anyway?
Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 07/10/2011 :  16:13:33  Show Profile  Visit jcelko's Homepage  Reply with Quote
quote:
Originally posted by cwildeman

Hi,
I am a novice SQL writer. I have a field [sic] called NoInjury which is a BIT field [sic]. When it is check on the ACCESS form that is used to create the records [sic], it means there is no injury for this record [sic] and when it is unchecked it means there is an injury. I want to write a query that counts the number of Injuries and am trying to figure out how to write an IF THEN statement that will assign a No or Unchecked value as a 1 and then sum or perform a count on the No values. Can someone help?
Chuck

Chuck W



Where to start? So much wrong in such a small posting (which did not have any DDL, which is very rude in SQL forums and newsgroups).

Fields are not columns, rows are not records, and tables are not files. Totally different concept and implementation.

We are the database; we do not care about ACCESS or any other front ends. We write a query and throw the data over the wall to the ne3xt tier in our tiered architecture.

We do not use BIT flags in SQL; that was assembly language in the 1950's. In your case, SQL programmers would look for an industry standard injury code. I believe the NFL has one, but there is also the proper sections o0f the ICD codes, but you need to do research. Oh, Good SQL people spend more time researching the data and encodings than writing DDL.

We do not write IF THEN statement when we can help it; that is 98+% of the time. We have a CASE expression. Expressions are not statements. Here is a skeleton for you.

SELECT something_data_element
SUM(CASE WHEN injury_code = '000.000' THEN 1 ELSE 0 END)
AS non-jury_cnt
FROM Foobar
GROUP BY something_data_element;


--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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22760 Posts

Posted - 07/11/2011 :  05:31:11  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by robvolk

quote:
Originally posted by jcelko

>> I am a novice SQL writer. <<

SELECT SUM(CASE WHEN WHEN injury_code = '0000' THEN ! ELSE 0 END)
AS no-injury_cnt,
SUM(CASE WHEN WHEN injury_code <> '0000' THEN ! ELSE 0 END)
AS injury_cnt
FROM Personnel
How does one SUM() an exclamation mark anyway?


Also hypen can not be used as part of column name without warpped by [] ,single or double quotes

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000