December 13, 2007 at 10:52 am
Folks,
I have a table and I am trying group two "select" ...
SELECT teste.tecnico, teste.nome (select sum(teste.qtd) from teste where teste.setor_dest= 'IRMOT' ) AS HF, (select sum(teste.qtd) from teste where teste.setor_dest= 'QCC') AS TT
FROM teste
GROUP BY teste.tecnico, teste.nome;
I would like to get the sum(teste.qtd) from teste where teste.setor_dest= 'IRMOT' ) AS HF
for other column I do
(select sum(teste.qtd) from teste where teste.setor_dest= 'QCC') AS TT
however is returning the first registro equal for all.
whar can be?
December 13, 2007 at 10:58 am
Some sample data would help... but try this.
SELECT
teste.tecnico,
teste.nome,
SUM(CASE WHEN teste.setor_dest= 'IRMOT' THEN teste.qtd ELSE 0 END ) as HF,
SUM(CASE WHEN teste.setor_dest= 'QCC' THEN teste.qtd ELSE 0 END ) as TT
FROM teste
GROUP BY teste.tecnico, teste.nome;
December 14, 2007 at 5:01 am
ayreshp (12/13/2007)
for other column I do
(select sum(teste.qtd) from teste where teste.setor_dest= 'QCC') AS TT
however is returning the first registro equal for all.
The problem in your query is, that you didn't correlate your (select SUM(..)) statements to any particular row. When you write such type of code, you always need to keep in mind that the part in parentheses is absolutely independent on the rest, unless you specify otherwise. So, in your query for each row, you calculate the sum over ALL rows - not just for the one tecnico.
The following should work, but I am posting it only for the sake of explaining what has caused the error.
Do NOT use it - correlated subqueries are generally bad for performance; use the above posted solution with CASE instead!
SELECT TT.tecnico, TT.nome,
(select sum(teste.qtd) from teste where teste.setor_dest= 'IRMOT'
AND tecnico=TT.tecnico AND nome=TT.nome) AS HF,
(select sum(teste.qtd) from teste where teste.setor_dest= 'QCC'
AND tecnico=TT.tecnico AND nome=TT.nome) AS TT
FROM teste TT
GROUP BY TT.tecnico, TT.nome
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply