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.
| Author |
Topic |
|
cwildeman
Starting Member
40 Posts |
 Posted - 2011-07-08 : 09:40:39
|
| 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?ChuckChuck W |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-08 : 10:10:55
|
| SELECT SUM(CASE WHEN NoInjury=1 THEN 1 END) NoInjuryTotal, SUM(CASE WHEN NoInjury=0 THEN 1 END) InjuryTotalFROM myTable |
 |
|
|
cwildeman
Starting Member
40 Posts |
Posted - 2011-07-08 : 10:40:54
|
| Thanks. That worked. ChuckChuck W |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-07-08 : 11:01:47
|
quote:
Originally posted by robvolk
SELECT SUM(CASE WHEN NoInjury=1 THEN 1 END) NoInjuryTotal, SUM(CASE WHEN NoInjury=0 THEN 1 END) InjuryTotalFROM 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.MadhivananFailing to plan is Planning to fail |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-07-08 : 15:01:16
|
| >> 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 PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-08 : 15:06:54
|
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? |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-07-10 : 16:13:33
|
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?ChuckChuck 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 PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-07-11 : 05:31:11
|
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 quotesMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
If then statement