I'm trying to implement an IF (SELECT) statement in an SQL query. The query looks like this so far:
SELECT *, IFNULL(duree_corrigee,duree) as
duree_final, IF((SELECT COUNT(*) FROM pointage_interval WHERE panier=1
AND pointage_employe_id = 'FH'
AND semaine = 23 GROUP BY date)>=1,
1,0) as panier_final FROM pointage_interval P
JOIN
employe E ON P.pointage_employe_id
= E.employe_id JOIN chantier C
ON C.chantier_id=P.pointage_chantier_id
WHERE (pointage_employe_id = 'FH'
AND semaine = 23)
When I run it, it returns me: #1242 - Subquery returns more than 1 row
Basically I can have many rows with the same date. One column is "panier", and can be either 1 or 0. I would like to create a new column panier_final, which takes the value 1 or 0 based on if there is a least one column with value of panier = 1 for the same day. So all the rows with this same date will get the same value 1 or 0 for panier_final.
If I run the query without the IF (SELECT), as this:
SELECT *, IFNULL(duree_corrigee,duree) as
duree_final FROM pointage_interval P
JOIN
employe E ON P.pointage_employe_id
= E.employe_id JOIN chantier C
ON C.chantier_id=P.pointage_chantier_id
WHERE (pointage_employe_id = 'FH'
AND semaine = 23)
I get this output:
As you can see, there are two rows for the same day, 2017-06-07. So I would like my IF (SELECT) query to create a new column (panier_final) for each row, the value of this new column being the same for each same date's rows. In this case each row would get a new column panier_final, the value being 1 since that for each given date, the values panier are equal or superior to 1
Aucun commentaire:
Enregistrer un commentaire