Transpose mysql query rows into columns

by

You can do it with a crosstab like this –

SELECT
    `year`,
    `month`,
    SUM(IF(`transporttype` = 'inbound',                 1, 0)) AS `inbound`,
    SUM(IF(`transporttype` = 'LocalPMB',                1, 0)) AS `LocalPMB`,
    SUM(IF(`transporttype` = 'Long Distance',           1, 0)) AS `Long Distance`,
    SUM(IF(`transporttype` = 'shuttle',                 1, 0)) AS `shuttle`,
    SUM(IF(`transporttype` = 'export',                  1, 0)) AS `export`,
    SUM(IF(`transporttype` = 'Extrusions-LongDistance', 1, 0)) AS `Extrusions-LongDistance`,
    SUM(IF(`transporttype` = 'Extrusions-Shuttle',      1, 0)) AS `Extrusions-Shuttle`
FROM `deliveries`
GROUP BY `year`, `month`

On a different note, you should move transporttype values to a lookup table and have transporttype_id in this table.

More Related Contents:

  1. MySql Transpose Row into Column and Column into Row [duplicate]
  2. How to transpose rows to columns with large amount of the data in BigQuery/SQL?
  3. Recreating a query using INNER JOIN? [duplicate]
  4. Create a Cumulative Sum Column in MySQL
  5. Equivalent of explode() to work with strings in MySQL
  6. mysql select dynamic row values as column names, another column as value
  7. Access denied for user ‘root’@’localhost’ (using password: YES) (Mysql::Error)
  8. Setting up MySQL and importing dump within Dockerfile
  9. MySQL DELETE FROM with subquery as condition
  10. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  11. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  12. Simple Random Samples from a Sql database
  13. node.js mysql error: ECONNREFUSED
  14. #1273 – Unknown collation: ‘utf8mb4_unicode_ci’ cPanel
  15. Not unique table/alias
  16. MySQL GROUP BY DateTime +/- 3 seconds
  17. MySQL foreign key to allow NULL?
  18. MySQL : When stored procedure parameter name is the same as table column name
  19. Dynamic conversion of string into column name. MySQL
  20. Does a UNIQUE constraint automatically create an INDEX on the field(s)?
  21. MySQL 5.0 indexes – Unique vs Non Unique
  22. MySQL – Select from a list of numbers those without a counterpart in the id field of a table
  23. sql group by versus distinct
  24. Accent insensitive search query in MySQL
  25. MySql – How get value in previous row and value in next row? [duplicate]
  26. Updating table in trigger after update on the same table
  27. For homebrew mysql installs, where’s my.cnf?
  28. MySQL Select Multiple VALUES
  29. Force MySQL to return duplicates from WHERE IN clause without using JOIN/UNION?
  30. Convert SQL Server query to MySQL [duplicate]

Leave a Comment