Let’s look at the MySQL crosstab query and it usages in detail

At Bobcares with our  MySQL support, we can give you a detailed note on the MySQL Crosstab query and its usages.

Do you want to learn more? Continue reading and get in touch if you have any additional queries.

Crosstab query

A MySQL cross-tabulation query is also known as a MySQL crosstab query for short. It can display the sum, average, count, or other sorts of total calculated on two or more variables. A table represents the findings. The field is down the left side of a matrix (row headings) and the other across the top (column headings).

Data saved in a database is frequently beneficial for statistical purposes as well. If a user owns an online store, they want to be able to generate a turnover report. The user can obtain statistical data by using GROUP BY, for example.

Example

For example, SELECT COUNT(*) AS ‘invoice count’, SUM(‘invoice’.’amount’) AS ‘turnover, DATE FORMAT(invoice.date, ‘percent M’) AS’month’ FROM `invoice` WHERE `date` BETWEEN '2022-01-01' AND '2022-12-31' GROUP BY MONTH(`invoice`.`date`)

month invoice_count turnover
January 84 9532.26
February 141 20857.61
March 91 10922.71
April 112 15044.48
May 101 9676.60
June 137 12860.88
July 281 34291.20
August 191 26377.66
September 103 16324.78
October 99 12873.23

If the user completes a large sales mark, they can see the turnover for each product category. The user might accomplish this with a simple GROUP BY as shown in the MySQL crosstab query below:

SELECT DATE_FORMAT(`invoice`.`date`, '%M') AS `month`, `category`.`description` AS `category`, COUNT(*) AS `product_count`, SUM(`invoice_product`.`amount`) AS `turnover'. INNER JOIN "invoice product" ON "invoice"

FROM “invoice” “id” equals “invoice product.” `invoice id` JOIN ‘product’ ON ‘invoice product’ WITH LEFT JOIN. “product id” equals “product.” ID LEFT JOIN CATEGORIES ON PRODUCT. Category = “category id” `id`. Here the `date` BETWEEN ‘2022-01-01’ AND ‘2022-12-31’ GROUP BY MONTH(`invoice`.`date`), `category`.id.

month category product_count turnover
January Hardware 62 4821.31
January Software 51 4419.41
January Cables 12 291.54
February Hardware 71 8408.93
February Software 101 11726.36
February Cables 17 312.32
February Other 2 410.00
March Hardware 21 2371.58
March Software 81 8238.81
March Cables 13 312.32 ...

This would give the user each category in a different row, ordered by month. The user can have 1 row every month with each category as a column as well as invoice information.

SELECT DATE_FORMAT(`invoice`.`date`, '%M') AS `month`, COUNT(DISTINCT `invoice`.`id`) AS `product_count`, COUNT(*) AS `invoice_count`, SUM(`invoice_product`.`amount`) AS `turnover`, SUM(`product`.`category_id`=1) AS `hardware_count`, SUM(IF(`product`.`category_id`=1, `invoice_product`.`amount`, 0)) AS `hardware_turnover`, SUM(`product`.`category_id`=2) AS `software_count`, SUM(IF(`product`.`category_id`=2, `invoice_product`.`amount`, 0)) AS `software_turnover`, SUM(`product`.`category_id`=3) AS `cables_count`, SUM(IF(`product`.`category_id`=3, `invoice_product`.`amount`, 0)) AS `cables_turnover`, SUM(`product`.`category_id`=4) AS `other_count`, SUM(IF(`product`.`category_id`=4, `invoice_product`.`amount`, 0)) AS `other_turnover`

‘invoice product’ IS JOINED TO ‘invoice’ FROM ‘invoice’. Id is same to “invoice product.” `invoice id` ON “invoice product,” LEFT JOIN “product.” Product = “product id” “id” LEFT JOINS “category” ON “product.” “category id” equals “category.” `id`

month invoice_count turnover hardware_count hardware_turnover software_count software_turnover cables_count cables_turnover other_count other_turnover
January 84 9532.26 62 4821.31 51 4419.41 12 291.54 0 0
February 141 20857.61 71 8408.93 101 11726.36 17 312.32 2 410.00
March 91 10922.71 21 2371.58 81 8238.81 13 312.32 0 0 ...

Finally, the main disadvantage of this technique is that after the introduction of a category, the user must alter the query. Hence the user can avoid this by dynamically constructing the query in PHP, Ruby, Python, Node.js, and other languages.

[Need assistance with similar queries? We are here to help]

Conclusion

To conclude, MySQL crosstab query is shortened from for cross-tabulation query. It can help the users to run and display sum, average, count, and other forms of calculations. The Crosstab query displays it on table-like structures for easy management and data accessibility.