Error in Mysql sub-query in Workbench

Asked

Viewed 73 times

0

Good morning! My code SQL is making the following mistake:

Error Code: 1242. Subquery Returns more than 1 Row 0,000 sec

The code is as follows::

SELECT DATE_FORMAT(FROM_UNIXTIME(data), '%Y') AS ano, COUNT(*) AS Proposta,
    (SELECT COUNT(*) FROM proposta WHERE nestado = 7 GROUP BY DATE_FORMAT(FROM_UNIXTIME(data), '%Y')) AS Adquiridas,
    (SELECT COUNT(*) FROM proposta WHERE nestado IN(2,8) GROUP BY DATE_FORMAT(FROM_UNIXTIME(data), '%Y')) AS Nao_Adquiridas,
    (SELECT COUNT(*) FROM proposta WHERE nestado IN(1,3,5,6) GROUP BY DATE_FORMAT(FROM_UNIXTIME(data), '%Y')) AS Em_Aquisicao
FROM proposta
GROUP BY DATE_FORMAT(FROM_UNIXTIME(data), '%Y')

I think the mistake is in this SELECT:

SELECT COUNT(*) FROM proposal WHERE nested IN(1,3,5,6) GROUP BY DATE_FORMAT(FROM_UNIXTIME(date), '%Y')

I’ve done tests and this is it SELECT the only one that resumes more than 1 line! How do I resolve this error?

mysql sql mysql-workbench

  • is grouping by year of date, does not return more than one pq has more than one year in the table? has tried to limit one year to test?

  • Maybe giving nicknames to your proposed table and subselects restrict the year for it to be equal to the outermost select.

  • Good afternoon Ricardo! It happens that if I limit only to 1 year it gives! but my goal is not to limit 1 year.

  • Good afternoon anonymously! In the case I have, the code will run first the selects that are within the main select! ie, I can not put "AS year" in the main select to then put "year" in the secondary selects!

  • 1

    See: https://dev.mysql.com/doc/refman/8.0/en/correlated-subqueries.html

  • @anonimo! that’s what I needed! now I get it! thanks!

Show 1 more comment
No answers

Browser other questions tagged mysql sql mysql-workbench

You are not signed in. Login or sign up in order to post.