MySQL correlated sub-query in SELECT clause

Question

I'm trying to execute a correlated sub-query in the SELECT clause of a MySQL query. How do I use the value of a row in another column inside the WHERE clause of my subquery?

I've been referencing the "Correlated Sub-Query Example" section of this webpage, but for some reason my query is not working the same.

Here is my code:

SELECT Year,
     ( SELECT COUNT(*)
       FROM systems
       WHERE SYSTEMTYPE = 'handheld' AND Year = sys.Year
     ) as handheld,
     ( SELECT COUNT(*)
       FROM systems
       WHERE SYSTEMTYPE = 'console' AND Year = sys.Year
     ) as console,
FROM systems as sys
WHERE Year IS NOT NULL

Basically, I'm trying to create a table that shows how many systems of each type were created for each year. When I run that query in MySQL Workbench it runs until the database connection expires. I can't see how this query is much different than the one on the website I am referencing.

Any help would be greatly appreciated! If it seems there is a better way I could go about this, I am open to those ideas as well. Thank you!

@GiorgosBetsos I’m sorry but I’m unaware of what properly indexed means! — Finley, Apr 26 '19 at 05:28
You can have a look at [this](https://stackoverflow.com/questions/3567981/how-do-mysql-indexes-work) post — Giorgos Betsos, Apr 26 '19 at 05:34

Michał Turczyn · Accepted Answer · 2019-04-26T05:45:45.350

3

This may be issue of performance of a query. Such subquery needs to be executed for each row, thus it may take long to run. The query can be simplified by using group by:

select year, count(*) from systems
where Year is not null and systemtype = 'handled'
group by Year

UPDATE regarding comment:

what if I want to add more columns for different types of systems other than just the one type?

Use query:

select year,
       sum(case when systemtype = 'handled' then 1 else 0 end) handled,
       sum(case when systemtype = 'console' then 1 else 0 end) console
from systems
where Year is not null
group by Year

edited Apr 26 '19 at 05:45

answered Apr 26 '19 at 05:29

Michał Turczyn

32,028
14
47
69

Thank you for your response! I’m sorry this wasn’t more clear, but what if I want to add more columns for different types of systems other than just the one type? Does that make sense? Is there a better way I could do that? – Finley Apr 26 '19 at 05:34
Perfect! That is exactly what I needed. Just tried it out and it worked great. Thank you very much! – Finley Apr 26 '19 at 05:54

score 2 · Answer 2 · answered Apr 26 '19 at 05:26

Could be you have a scope issue try use a inner join instead of a subquery for each rows

SELECT sys.Year, t.my_count
FROM systems as sys
INNER JOIN 
  ( SELECT Year, COUNT(*) my_count
    FROM systems
    WHERE SYSTEMTYPE = 'handheld' AND Year = sys.Year
    GROUP BY year
) t on t.year = sys.year
WHERE sys.Year IS NOT NULL

score 2 · Answer 3 · answered Apr 26 '19 at 05:28

2

Use Group By statement instead of subqueries. Subqueries will make your query run much slower as more rows are added. Try this query to get number of systems made per type and per year:

SELECT
    Year, 
    SYSTEMTYPE, 
    COUNT(*) as Total_systems_per_type_per_year
FROM systems
WHERE Year IS NOT NULL
GROUP BY Year, SYSTEMTYPE

answered Apr 26 '19 at 05:28

Kevin HR

249
1
6

Thank you for your response! I’m sorry this wasn’t more clear, but what if I want to add more columns for different counts of different types per year? For example, if I added a type of `console` and I wanted that count in a separate column as well. Is there a way I could do that? – Finley Apr 26 '19 at 05:40
1

You will probably need to add columns manually by doing something like this: `SELECT Year, SUM(IF(SYSTEMTYPE='console',1,0)) as Total_console_per_year, SUM(IF(SYSTEMTYPE='handheld',1,0)) as Total_handheld_per_year FROM systems WHERE Year IS NOT NULL GROUP BY Year` – Kevin HR Apr 26 '19 at 05:43

MySQL correlated sub-query in SELECT clause

3 Answers3