CODEWITHSUNDEEP
mysqlsql
Hat na
Hat na

Reputation: 55

How can I fix the "subquery returns more than 1 row" error?

I'm getting the Subquery returns more than 1 row error in MySQL. 

SELECT  variable.libelle_variable,
    donnee.statistique,
    (SELECT SUM(do.statistique) 
     FROM `donnee` as do , `quartier`, `type_donnee`, `variable`, `donnee`, `associer` 
     WHERE
          (type_donnee.libelle_type_donnee = "Activités commerciales" 
       AND associer.code_quartier = quartier.code_quartier 
       AND associer.id_donnee = donnee.id_donnee 
       AND associer.id_variable = variable.id_variable 
       AND associer.id_type_donnee = type_donnee.id_type_donnee 
       AND do.id_donnee = associer.id_donnee 
          ) 
     GROUP BY variable.libelle_variable 
    )
FROM 
    `quartier`, 
    `type_donnee`,
    `variable`,
    `donnee`,
    `associer`
 WHERE 
    type_donnee.libelle_type_donnee = "Activités commerciales"
 AND
    quartier.nom_quartier = "Hôpitaux-facultés"
 AND 
    associer.code_quartier = quartier.code_quartier 
 AND 
    associer.id_donnee = donnee.id_donnee 
 AND 
    associer.id_variable = variable.id_variable 
 AND 
    associer.id_type_donnee = type_donnee.id_type_donnee 
 GROUP BY  
    variable.libelle_variable

The problem is that my Subquery returns only one row when I test it alone.

Upvotes: 1

Views: 523

Answers (1)

D-Shih
D-Shih

Reputation: 46219

The problem is in the select subquery.

From your question you don't need to use subquery on select

Avoid use CROSS JOIN.

You can use inner join instead of CROSS JOIN.

SELECT  variable.libelle_variable,
        donnee.statistique,
        SUM(do.statistique)
    FROM 
        `associer`
        INNER JOIN  `quartier` ON associer.code_quartier = quartier.code_quartier 
        INNER JOIN `type_donnee` ON associer.id_type_donnee = type_donnee.id_type_donnee 
        INNER JOIN `variable` ON associer.id_variable = variable.id_variable 
        INNER JOIN `donnee` ON associer.id_donnee = donnee.id_donnee 
     WHERE 
        type_donnee.libelle_type_donnee = "Activités commerciales"
     AND
        quartier.nom_quartier = "Hôpitaux-facultés"
     GROUP BY  variable.libelle_variable,donnee.statistique

EDIT

You can try this.

SELECT  
    variable.libelle_variable,
    donnee.statistique,
    (
     SELECT SUM(do.statistique) 
     FROM `donnee` as do , 
          `quartier`, 
          `type_donnee`, 
          `variable` AS T1, 
          `donnee`, 
          `associer` 
     WHERE
          (
              type_donnee.libelle_type_donnee = "Activités commerciales" 
           AND associer.code_quartier = quartier.code_quartier 
           AND associer.id_donnee = donnee.id_donnee 
           AND associer.id_variable = T1.id_variable 
           AND associer.id_type_donnee = type_donnee.id_type_donnee 
           AND do.id_donnee = associer.id_donnee 
           AND T1.libelle_variable = variable.libelle_variable
          ) 
    )
FROM 
    `associer`
    INNER JOIN  `quartier` ON associer.code_quartier = quartier.code_quartier 
    INNER JOIN `type_donnee` ON associer.id_type_donnee = type_donnee.id_type_donnee 
    INNER JOIN `variable` ON associer.id_variable = variable.id_variable 
    INNER JOIN `donnee` ON associer.id_donnee = donnee.id_donnee 
 WHERE 
    type_donnee.libelle_type_donnee = "Activités commerciales"
 AND
    quartier.nom_quartier = "Hôpitaux-facultés"
 GROUP BY  
     variable.libelle_variable,
     donnee.statistique

Upvotes: 1

Related Questions