Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL summing with WHERE condition based on distinct values

I have the following table, lets call this table_a:

Zone vehicle_name vehicle_status
A BusA1 Start
A BusA2 Start
A BusA3 Stopped
B BoatB1 Ready
B BoatB2 Stopped
B BoatB3 Start
C CarC1 Ready
C CarC2 Ready
C CarC3 Stopped

I would like to create a new table from the table_a :

Zone no_of_vehicle no_of_start no_of_ready no_of_stopped
A 3 2 0 1
B 3 1 1 1
C 3 0 2 1

What would be the most efficient way to this?

E.g.

SELECT DISTINCT(ZONE) AS ZONE,
       SUM(IF ZONE = 'A' THEN ... IF ZONE = 'B' THEN ...
FROM table_a
Tags:

2

Answers


  1. 0 
    SELECT 
  Zone,
  COUNT(DISTINCT vehicle_name) AS no_of_vehicle,
  SUM(vehicle_status = 'Start') AS no_of_start,
  SUM(vehicle_status = 'Ready') AS no_of_ready,
  SUM(vehicle_status = 'Stopped') AS no_of_stopped
FROM table_a
GROUP BY Zone;

    This single SQL query achieves the desired result using conditional aggregation and grouping by the ‘Zone’.

    Login or Signup to reply.
  2. 0 
    SELECT
    Zone,
    COUNT(vehicle_name) AS no_of_vehicle,
    SUM(CASE WHEN vehicle_status = 'Start' THEN 1 ELSE 0 END) AS no_of_start,
    SUM(CASE WHEN vehicle_status = 'Ready' THEN 1 ELSE 0 END) AS no_of_ready,
    SUM(CASE WHEN vehicle_status = 'Stopped' THEN 1 ELSE 0 END) AS no_of_stopped
FROM
    table_a
GROUP BY
    Zone;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search