0
Hello, I would like a support to understand why the following two querys produce different results. The strategy I searched with them is the same, to count and group Sip results with different columns in the table, but I had to opt for the second query because of performance with a high volume of data. the first query is a pivot in the table:
SELECT
id_tabela,
SUM(CASE WHEN coluna1_sip = x THEN 1 ELSE 0 END) as 'x1',
SUM(CASE WHEN coluna1_sip = y THEN 1 ELSE 0 END) as 'y1',
SUM(CASE WHEN coluna1_sip = z THEN 1 ELSE 0 END) as 'z1',
.
.
.
SUM(CASE WHEN coluna11_sip = x THEN 1 ELSE 0 END) as 'x11'
from tabela group by id_tabela
the second way, below, was to replicate the above results in a less costly way
SELECT
SUM((CASE WHEN coluna1_sip = x THEN 1 ELSE 0 END) +
(CASE WHEN coluna2_sip = x then 1 ELSE 0 END) +
.
.
.
(CASE WHEN coluna11_sip = x then 1 ELSE 0 END)) AS 'x',
SUM((CASE WHEN coluna1_sip = y then 1 else 0 end) +
(CASE WHEN coluna2_sip = y then 1 else 0 end) +
.
.
.
(CASE when coluna11_sip = y then 1 else 0 end)) as 'y'
from tabela
I would like help to understand why the results of the sum of the second way (adding the counters) are not compatible with that of the first (bring all as pivot and add up later), I greatly appreciate the help.
Group by "id_table" ? No 2.
– Motta
In the second it was not necessary to put, but in the first, yes. I will take advantage to edit the first. Even with the grouping in the 2nd there was no difference in the final result.
– wbs2a
It would be good to sample the data to know what you are getting and what you were hoping to get. Try creating a template on http:/sqlfiddle.com/ and edit your question including the url to that template, so we can simulate the problem and the solution.
– Clarck Maciel
I’ll create yes, thank you very much for the tip, Clarck!
– wbs2a