Efficient Subtotalling in MySQL

I quite often find when developing a site or an application that uses an SQL database that I have to produce multiple subtotals from a single table and/or using very complex rules and most of the time I’m trying to generate these totals using SQL only to minimize the workload on a system. While there are many ways to do this by far the most efficient and flexible way I’ve found is to use the aggregate function SUM() and IF() in MySQL.

For example, recently while working on Car Arena, we needed to produce a report for the site owners which contained the number of trade cars, private cars and the total number of cars submitted per day. Not only was this report to be as lightweight as possible (as it would display all dates on a single page) but also writing a query which would produce the report without calculating the product of a conditional statement would possibly mean writing subqueries and overcomplicating the SQL.

So for this report my solution was to count the number of records in the database which had its trade valuation flag set to true and the total number of cars grouped into days, months and years. The conditional statement IF produces the value of 1 or 0, depending on the logic for the field, which is then passed to SUM to ‘count’ the number of records in the group. For example:

SUM(IF(TradeValuation, 1, 0)) As Trade

While TRUE and FALSE can be cast to an unsigned integer type MySQL defines TRUE as being anything but zero. TRUE and FALSE are simply aliases for 1 and 0 respectively and while often you find zero and one being used as boolean values within a database using IF() ensures you never fall fowl of the exception to the rule and future proofs the statement somewhat.

The whole statement for this report which calculates the subtotals for the number of cars posted was just:

Just one pass on the Valuations table is needed to generate the subtotals for the report so responses are quick and do not require much of the web servers resources.

About tc

tc is a Hull based Computer Programmer with over 15 years experience in Software Development. He has developed countless Multi-Tear Desktop Applications and web applications for business in both the UK and abroad. Currently tc spends most of his time developing web sites / applications in PHP and desktop software in C#
This entry was posted in SQL and tagged , . Bookmark the permalink.

Comments are closed.