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`)Copy Code

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.23Copy Code

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"Copy Code

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`.

idCopy Code

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 ...Copy Code

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`Copy Code

‘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 ...  Copy Code

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.