Xion Xion - 4 months ago 6
SQL Question

SQL query variable in union + COUNT()

I need help with an SQL query where i need to

SELECT MachineID from TABLE1
,

and then

Count(Passfail) from TABLE2 WHERE MachineID= *MACHINEID


from the SELECT statement previously* into a single table

Answer

You've almost written it right there. You need to read up a bit on joins and sql group by :

select T1.MachineId, count(T2.Passfail) from
Table1 T1 inner join Table2 T2 on T1.MachineId = T2.Machine Id
group by T1.MachineId

EDIT: For a separate count depending on values of PassFail, I'd use:

select T1.MachineId, 
sum(case when T2.PassFail='pass' then 1 else 0 end) as 'pass',
sum(case when T2.PassFail='fail' then 1 else 0 end) as 'fail'
from Table1 T1 inner join 
Table2 T2 on T1.MachineId = T2.machineId 
group by T1.machineId 
Comments