CODEWITHSUNDEEP
sqlsql-server
Mr.Bear
Mr.Bear

Reputation: 37

SQL - Using CASE and AVG

For a database of Parking Citations, I wrote the following syntax to display a segment for each Citation that falls within certain ViolFine parameters:

SELECT Citation, Make, ViolCode, ViolDate, ViolFine
    ,CASE 
        WHEN ViolFine BETWEEN 0.00 AND 50.00 THEN '01. $0.00 - $50.00'
        WHEN ViolFine BETWEEN 50.01 AND 100.00 THEN '02. $50.01 - $100.00'
        WHEN ViolFine > 100 THEN '03. larger than $100.00'  
      END AS ViolFineSegments
FROM dbo.ParkingCitations
ORDER BY ViolDate DESC

Now I want to list the number of citation and the average citation fine for each segment. I used the below, but it appears to be listing just each ViolFine and the total number of citations along with segment parameter:

SELECT AVG(ViolFine) AS AvgFine, COUNT(Citation) AS NumberViolations
    ,CASE 
        WHEN AVG(ViolFine) BETWEEN 0.00 AND 50.00 THEN '01. $0.00 - $50.00'
        WHEN AVG(ViolFine) BETWEEN 50.01 AND 100.00 THEN '02. $50.01 - $100.00'
        WHEN AVG(ViolFine) > 100 THEN '03. larger than $100.00' 
      END AS ViolFineSegments
FROM dbo.ParkingCitations
GROUP BY  ViolFine
ORDER BY ViolFine

Essentially, I want just want three rows based off the segment and I'm not sure how to proceed.

Upvotes: 2

Views: 5001

Answers (2)

Zorkolot
Zorkolot

Reputation: 2027

Now I want to list the number of citation and the average citation fine for each segment.

If it's by segment then you'll need to group by the ViolFineSegments column. The aggregates you request are a count, and the average(ViolFine). I would try subquerying the original code for the aggregates:

SELECT 
    ViolFineSegments,
    COUNT(ViolFine) AS [Number of Citations],
    AVG(ViolFine) AS [Average Fine]
FROM 
    (SELECT 
         ViolFine,
         CASE 
            WHEN ViolFine BETWEEN 0.00 AND 50.00 THEN '01. $0.00 - $50.00'
            WHEN ViolFine BETWEEN 50.01 AND 100.00 THEN '02. $50.01 - $100.00'
            WHEN ViolFine > 100 THEN '03. larger than $100.00'  
         END AS ViolFineSegments
     FROM 
         dbo.ParkingCitations) AS dT
GROUP BY 
    ViolFineSegments
ORDER BY 
    [Average Fine]

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270443

The CASE needs to be the argument of the aggregation function. I think you want:

SELECT AVG(ViolFine) AS AvgFine, COUNT(Citation) AS NumberViolations,
       AVG(CASE WHEN ViolFine BETWEEN 0.00 AND 50.00 THEN ViolFine END) as [01. $0.00 - $50.00],
       AVG(CASE WHEN ViolFine BETWEEN 50.01 AND 100.00 THEN ViolFine END) as [02. $50.01 - $100.00],
       AVG(CASE WHEN ViolFine > 100 THEN ViolFine END) as [03. larger than $100.00]
FROM dbo.ParkingCitations;

This returns one row, with five columns. For the numbers, you can do:

SELECT COUNT(Citation) AS NumberViolations,
       SUM(CASE WHEN ViolFine BETWEEN 0.00 AND 50.00 THEN 1 ELSE 0 END) as cnt_000_050,
       SUM(CASE WHEN ViolFine BETWEEN 50.01 AND 100.00 THEN 1 ELSE 0 END) as cnt_050_100,
       SUM(CASE WHEN AVG(ViolFine) > 100 THEN 1 ELSE 0 END) as cnt_100pl,
       AVG(ViolFine) AS AvgFine, 
       AVG(CASE WHEN ViolFine BETWEEN 0.00 AND 50.00 THEN ViolFine END) as avg_000_050,
       AVG(CASE WHEN ViolFine BETWEEN 50.01 AND 100.00 THEN ViolFine END) as avg_050_100
       AVG(CASE WHEN ViolFine > 100 THEN ViolFine END) as avg_100pl
FROM dbo.ParkingCitations;

Upvotes: 4

Related Questions