CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ravi Kanth
Ravi Kanth

Reputation: 1210

How to get count of different value from a same columns in sql?

I have a table named VehicleAlerts, you can see in the below image that there are different type AlertSubCategory such as Pickup/Drop Delay ,Vehicle Stalled ,Vehicle Speeding Geofence Violation. How will I get count of all type of AlertSubCategory of a vehicle?

enter image description here

Upvotes: 0

Views: 62

Answers (4)

Navin Bhandari
Navin Bhandari

Reputation: 192

SELECT AlertSubCategory,COUNT(*) as norow FROM VehicleAlerts group by AlertSubCategory

Upvotes: 0

NOBLE M.O.
NOBLE M.O.

Reputation: 194

If you want to get the count of all vehicles in each AlertSubCategory then you can simple group it by AlertSubCategory and take the count as given below.

SELECT AlertSubCategory, COUNT(Id) AS Count
FROM VehicleAlerts
GROUP BY AlertSubCategory

If you want to get the cout of one vehicle in each AlertSubCategory then use a WHERE clause in this sql query as given below.

DECLARE @VehicleId INT = 1
SELECT AlertSubCategory, COUNT(Id) AS Count
FROM VehicleAlerts
WHERE Vehicle = @VehicleId 
GROUP BY AlertSubCategory

Change the value of @VehicleId as you required.

Upvotes: 0

Serpiton
Serpiton

Reputation: 3684

A simple Count with a Group By on both VehicleId and AlertSubCategory will get the data

SELECT VehicleId, AlertSubCategory, Count(1) Occurrence
FROM   [table]
GROUP BY VehicleId, AlertSubCategory

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93734

Use Conditional Count to do this

SELECT vehicleid, 
       P_D_delay_count = Count(CASE 
                                 WHEN alertsubcategory = 'pickup/drop delay' THEN 1 
                               END) AS, 
       VehicleStalled = Count(CASE 
                                WHEN alertsubcategory = 'VehicleStalled' THEN 1 
                              END), 
       VehicleSpeeding = Count(CASE 
                                 WHEN alertsubcategory = 'VehicleSpeeding' THEN 1 
                               END), 
       Geofence_Violation_count = Count(CASE 
                                          WHEN alertsubcategory = 'Geofence Violation' THEN 1 
                                        END) 
FROM   yourtable 
GROUP  BY vehicleid

Upvotes: 1

Related Questions