Need help with percentage query 1

[deepsheep]

I need help with a percetage query.
thread183-1330035 seemed to be close, but didn't quite work the way I need it to. It used the total count, not the count for a specific unit.

I need to pick the units that report a certain value in a certain field more than 20 percent in a given timeframe. Each unit may submit a differnt number of reports.

What I basically want is to divide the number of "bad" reports by the total number for each unit and attach that percentage to the master unit table.

I'm not sure I can do this in one query and would appreciate feedback on the best way to accomplish my goal.

Thanks!

 

[AlexCuse]

can you show sample data, especially how you know if a report is bad or good? If so, I think I have an answer for you.



Ignorance of certain subjects is a great part of wisdom

 

[deepsheep]

Thanks for helping!

The table I have has about 30 fields and a report per minute per unit in it. I'm looking at the Unit field (so I know which unit) and a GPS latitude field and a longitude field. If either lat or long is 0 then the report is bad. If more than 20% of reports for that unit have a 0 long or lat, the device may need maintenance.

It should look something like this :
Unit Lat Long Datetime
2222 44.56 -110.25 3/4/7 12:05:01 AM
A bad one:
2222 0 0 3/4/7 12:01:01 AM

If the unit is off, I won't get data, so for the same week each unit will have a different number of reports.

I'll need to join the results of this query with another table (it has one entry per unit) to get the rest of the information about the unit for the maintenance guys.

I hope this is what you needed!

 

[AlexCuse]

Hi DeepSheep,

I think this will work for you:

[COLOR=blue]select[/color] unit
, (1.00 * sum ( [COLOR=blue]case[/color] [COLOR=blue]when[/color] (Lat = 0 or [COLOR=blue]Long[/color] = 0) [COLOR=blue]then[/color] 1 [COLOR=blue]else[/color] 0 [COLOR=blue]end[/color]))/sum(1) [COLOR=blue]as[/color] Percentage
[COLOR=blue]from[/color] MyTable
[COLOR=blue]group[/color] [COLOR=blue]by[/color] unit

Basically what you do is take 1.00 * (number of bad reports), and divide the result by the total number of reports (for each unit, of course).

The reason for multiplying by 1.00 is to stop SQL Server from doing integer math (it forces an implicit cast to decimal (or maybe numeric) (x, 2))

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[deepsheep]

Brilliant! I wouldn't have thought to do it that way.

Thanks a bunch!