Need to calculate a total - 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

Analysis Server and Reporting Services (2008)

Need to calculate a total

Author

Topic

db2mo
Starting Member

1 Post

Posted - 2011-04-18 : 15:29:57

I have a dataset which counts the number of cirucumstances contributing to an accident. So, there could be many circumstances to one accident number. I then need to total the number of accidents alone. So, regardless of how many circumstances there are, I just need to count the accident number once. I cannot figure out how to get the calculation to count just the accident number regardless of the circumstances. Here is my current dataset.

SELECT (CASE Circumstance WHEN '01' THEN 'Vehicle Defects' WHEN '02' THEN 'Traffic Control Missing/Inoperable' WHEN '03' THEN 'Improperly Stopped on Roadway'WHEN '04' THEN 'Speed' WHEN '06' THEN 'Improper Passing' WHEN '07' THEN 'Violation Stop Sign/Signal' WHEN '08' THEN 'Wrong Side Not Passing' WHEN '09' THEN 'Following Too Close' WHEN '10' THEN 'Improper Signal' WHEN '11' THEN 'Improper Backing' WHEN '12' THEN 'Improper Turn' WHEN '13' THEN 'Improper Lane Usage/Change' WHEN '14' THEN 'Wrong Way One Way Street' WHEN '15' THEN 'Improper Start From Park' WHEN '16' THEN 'Improperly Parked' WHEN '17' THEN 'Failed To Yield' WHEN '18' THEN 'Drinking' WHEN '19' THEN 'Drugs' WHEN '20' THEN 'Physical Impairment' WHEN '21' THEN 'Inattention' WHEN '22' THEN 'Twenty-two' WHEN 'U ' THEN 'U' END) AS "CRSHCIRCUM", ACCNT
FROM TABLE
(SELECT CASE QO05DR_VH_CONT_CIRC WHEN '05' THEN '04' ELSE QO05DR_VH_CONT_CIRC END AS Circumstance,
COUNT(DISTINCT a.QO01ACCIDENT_NO) AS ACCNT
FROM XTECH.TR10TBACCIDENT a, XTECH.TR10TBVEHICLE_CIRCUMSTANCES vc
WHERE (QO01ACCIDENT_YEAR IN ('2008', '2009', '2010')) AND (QO01SUBMIT_AGENCY_ORI = 'MO0840100') AND
(vc.FK_QO01ACCIDENT_NO = a.QO01ACCIDENT_NO) OR
QO01ACCIDENT_YEAR IN ('2008', '2009', '2010')) AND (vc.FK_QO01ACCIDENT_NO = a.QO01ACCIDENT_NO) AND
(QO01MUNICIPALITY = '0230')
GROUP BY CASE QO05DR_VH_CONT_CIRC WHEN '05' THEN '04' ELSE QO05DR_VH_CONT_CIRC END) AS "DATA"

edit: removed code tags to fix long line

sonjan
Starting Member

22 Posts

Posted - 2011-04-19 : 17:48:59

I had the same problem. To find distinct records (eliminating duplicate rows), I used a group by and having functions eg
select
accnt
from
xtech
where
qo01accident_year in ('2008','2009','2010)
group by
accnt
having
count(*) > 1
order by
accnt asc

Hope this helps you.
Regards
Sonja

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources