Skip to main content

Some subquery magic with MySQL

As part of a Rails project I had to gather up some data from a single table, and calculate various stats on the data

Problem was - had to group data by a field, then calculate aggregates in various ways not possible with a single GROUP BY query

Using the SQL cookbook tips, and some pondering, I learned that I could create a bunch of 'fake' tables from the same table using subqueries, then join these tables in a way that would yield a single resultset

An example:

Let us say you have a table of person, their test points whether they passed the test attempt, like (two people a and b tried a test at various times) :












NamePointsPassed
a10 1
a25 0
b21 1
a28 0
b23 1
a35 1
b31 1
a35 1
b41 1


You want a result like





Name Avg AgeNo. of attempts'passed'
a 30.3 5
b 25.1 4
(caution: These numbers are probably wrong just too lazy to fix them up!)

As you can see, it is not possible with a single query, since grouping by name and aggregating for average will give you column 2 of the results, but how about column 3? column 3 is the no. of records which indicate a 1 for passed for each Name

To achieve this in a single query, do the following


Create a subquery to hold the average age results

SELECT avg(Age) as avgage from table a GROUP BY Name


Create a subquery to hold the no. of passed points per Name

SELECT count(0) as passed FROM table WHERE Passed=1 GROUP BY Name

Now join the two subqueries
SELECT t.Name, q1.avgage, q2.passed FROM table t
  LEFT JOIN
  ( SELECT avg(Age) as avgage from table a GROUP BY Name ) q1
  ON q1.Name=t.Name

  LEFT JOIN
  ( SELECT count(0) as passed FROM table WHERE Passed=1 GROUP BY Name ) q2
  ON q2.Name=t.Name

ORDER BY Name

the left join is not obvious in this case, but you could end up with missed matchse in more complex queries (then it is useful to use IFNULL() MySQL function to detect nulls and set values other than null in those cases)

An example of a real query is :


SELECT xx.label, xx.avg_time, xx.min_time, xx.max_time, xx.std_time,
IFNULL(yy.cnt_s,0) AS s,IFNULL(zz.cnt_f,0) AS f,
(IFNULL(yy.cnt_s,0) + IFNULL(zz.cnt_f,0)) AS totalsamples
FROM
# table 1, averages
( SELECT label,
AVG(responseTime) AS avg_time,
MIN(responseTime) AS min_time,
MAX(responseTime) AS max_time,
STD(responseTime) AS std_time
FROM rawdatas
WHERE jmetersession_id = 1
GROUP BY label ) xx
LEFT JOIN
# table 2, successes
(SELECT b.label, COUNT(0) AS cnt_s FROM rawdatas b
WHERE b.jmetersession_id = 1 AND b.success=1 GROUP BY b.label ) yy
ON yy.label=xx.label
# table 3, failures
LEFT JOIN
(SELECT label, COUNT(0) AS cnt_f FROM rawdatas
WHERE jmetersession_id = 1 AND success=0 GROUP BY label ) zz
ON zz.label=xx.label


Hope you find this useful!
Labels:

Comments

Post a Comment

Popular posts from this blog

Authenticating Spring Boot based application against secure LDAP/AD server

Authenticating against an Active Directory setup is quite common in organizations using Spring Boot / Spring Security can be a pain if you don't know exactly the requirements. I needed to add auth in my web app and secure some but not all endpoints of the application. My story was, I needed Spring security to authenticate against my company LDAP server which uses Active Directory I started by using the standard LDAP guide such as this which are all over the Internet, https://spring.io/guides/gs/authenticating-ldap/ and was able to setup the basic framework However, only test level LDAP auth was working for me, when I tried to auth against the company LDAP secure server, I had to resolve a few issues After 1 week and working with several devs at the company, I finally found why it was not working and the fix was easy Since I spent a week or so resolving this, I wanted to write this up in case someone finds this useful. Here is what I did (it was easy until the fourth ...
Post a Comment
Read more

Unit testing code that uses environment variables and system properties with fakes

I did not exactly learn this today, but I am sharing it as I have found it extremely useful when unit testing code that depends on environment or system property settings. While I am using Java as an example, the general concepts apply any where. Problem : You have a piece of code you are unit testing that uses settings from env variables or system properties passed to the VM (System.getProperty), but you don't want the tests to be affected by the 'real' environment or system properties in the VM. So, your unit tests should not get different results or fail when the real environment changes. Solution : There are several. But the most straightforward is to use a mocking library to mock out the environment or fake it out, whatever your prefer. You can create a fake using a library like EasyMock, PowerMock etc. This I won't discuss in this post, since there are numerous articles for that. Or you can write a simple class that acts as a proxy, using the proxy pattern...
Post a Comment
Read more

Using custom conditional logic to enable/disable Spring components

If you have a Spring component and you don't want it to load, you can use Spring's predefined conditionals as much as possible. For example, @Component   @ConditionalOnNotWebApplication   public class SchedulerEntryPoint implements ApplicationRunner { ...  } This will not load your component when running in non web application mode. Such as you may want to start the application but without any of the web framework using SpringApplicationBuilder. But sometimes you want to use custom conditions. It's pretty easy to do so, just use something like this @Component @Conditional (SchedulerCheck. class ) public class SchedulerEntryPoint implements ApplicationRunner { public static class SchedulerCheck implements Condition { @Override public boolean matches(ConditionContext conditionContext, AnnotatedTypeMetadata annotatedTypeMetadata) { return System. getProperty ( "scheduler" ) != ...
Post a Comment
Read more