Total column in a pivot example

Question

check here for background if needed: Pivoting a table with parametrization

We have 3 tables.

tid_color - parametrization table

--------------------------
ID         ColorDescription
--------------------------
1          Green
2          Yellow
3          Red
-------------------------

tid_car - parametrization table

--------------------------
ID         CARDescription
-------------------------
1          Car X
2          Car Y
3          Car Z
-------------------------- 

table_owners_cars

------------------------------------------------
ID         CarID       ColorID      Owner
------------------------------------------------
1           1             1           John
2           1             2           Mary
3           1             3           Mary  
4           1             3           Giovanni     
5           2             2           Mary
6           3             1           Carl
7           1             1           Hawking
8           1             1           Fanny 
------------------------------------------------

CarID is FOREIGN KEY to tid_car

ColorId is FOREIGN KEY to tid_color

If we code:

SELECT tcar.CarDescription, tco.ColorDescription, Count(*) as Total
FROM table_owners_cars tocar
LEFT JOIN tid_color tco ON tco.Id = tocar.ColorId
LEFT JOIN tid_Car tcar ON tcar.Id = tocar.CarId
GROUP BY CarDescription, ColorDescription 

it results as:

Id CarDescription     ColorDescription  Total
1       CarX               Green          3
2       CarX               Yellow         1
3       CarX               Red            1
4       CarY               Yellow         1
5       CarZ               Green          1

I want to pivot exactly as follows:

   ---------------------------------------------
   Id        Car     Green Yellow  Red    Total
   ---------------------------------------------
    1       CarX        3     1     1       5
    2       CarY        0     1     0       1                
    3       CarZ        1     0     0       1                   
   ---------------------------------------------

Now: we want to count the total for each row in a particular column of the table_owners_cars and this value is close to total like we see in the last column (between parenthesis). There are CarX WITH a NULL for the colorID (same can happen with the other Car) and we want to know all the number of carX, carY, CarZ (with and without (=null or 0) assigned ColorId

   ---------------------------------------------------
   Id        Car     Green Yellow  Red  Violet   Total
   ---------------------------------------------------
    1       CarX        3     1     1     0     5 (40)
    2       CarY        0     1     0     0     1 (35)               
    3       CarZ        1     0     0     0     1 (4)                   
   ---------------------------------------------------
   DESIRED TABLE

One try with the code (very similar to one provided in the aforementioned hyperlink):

SELECT pvt.CarID, tc.Description AS Car, CONCAT (' [1] as 'Green', [2] as 'Yellow', [3] as 'Red', [1]+[2]+[3] as 'total'', '(', count(*), ')' )
        FROM
        (SELECT CarID, colorId
         FROM table_owners_cars tocar
         ) p 
        PIVOT
        ( 
         COUNT (ColorId)
         FOR ColorId IN ( [1], [2], [3]) 
        ) AS pvt
        INNER JOIN tid_car tc  ON pvt.CarId=tc.Id
        group by p.Car

this does not work. single quotes are also a nightmare with concat. Thanks in advance.

Answer 1

I just find these queries easier to do with conditional aggregation:

SELECT CarId, Description,
       SUM(CASE WHEN color = 'Green' THEN 1 ELSE 0 END) as Green,
       SUM(CASE WHEN color = 'Yellow' THEN 1 ELSE 0 END) as Yellow,
       SUM(CASE WHEN color = 'Red' THEN 1 ELSE 0 END) as Red,
       SUM(CASE WHEN color IN ('Green', 'Yellow', 'Red') THEN 1 ELSE 0 END) as total_gyr,
       COUNT(*) as total
FROM table_owners_cars tocar
GROUP BY CarId, Description;

I see no reason to combine the two totals into a single string column -- as opposed to having them in separate integer columns. But, you can combine them if you want.