How can I merge 2 rows in SQL
I'm trying to create a stored procedure to retrieve value from multiple. The problem is in Table A there's a boolean type (isLocked) and I have to create a column for each state ('Locked' and 'Unlocked'). Here's the query that I wrote:
SELECT Machine.Machine_ID,
SUM(FinanceDonnee.CoutHoraire) AS Total,
CASE
WHEN FinanceDonnee_isLocked = 1
THEN
SUM(FinanceDonnee.CoutHoraire)
ELSE
0
END
AS Locked,
CASE
WHEN FinanceDonnee_isLocked= 0
THEN
SUM(FinanceDonnee.CoutHoraire)
ELSE
0
END
AS Unlocked
FROM ICR INNER JOIN
FinanceDonnee ON Machine.Machine_ID = FinanceDonnee.FinanceDonnee_Machine_ID
GROUP BY ICR_ID, FinanceDonnee.FinanceDonnee_isLocked
But It's not wor开发者_如何转开发king well because it duplicate the Machine_ID, one for locked and the other one for unlocked:
ID Locked Unlocked
4D9646EC 0 2
4D9646EC 12 0
I would like to have this view:
ID Locked Unlocked
4D9646EC 12 2
How can I do it?
Thanks.
Simply do a sum on locked,unlocked as so:
select sum(locked) as Locked ,sum(unlocked) as Unlocked, ID
from tablea
inner join tableb on tablea.ID=tableb.ID
group by ID
Using your sql, I think this should work:
SELECT machine_id AS id,
SUM(locked) AS locked,
SUM(unlocked) AS unlocked
FROM (SELECT machine.machine_id,
SUM(financedonnee.couthoraire) AS total,
CASE
WHEN financedonnee_islocked = 1 THEN SUM(
financedonnee.couthoraire)
ELSE 0
END AS locked,
CASE
WHEN financedonnee_islocked = 0 THEN SUM(
financedonnee.couthoraire)
ELSE 0
END AS unlocked
FROM icr
INNER JOIN financedonnee
ON machine.machine_id = financedonnee.financedonnee_machine_id
GROUP BY icr_id,
financedonnee.financedonnee_islocked) t
GROUP BY t.machine_id
精彩评论