Group By clause changes the results

I have a query like

SELECT weeks_ind, weeks_rol, weeks_no, date,
CASE WHEN SUM(CASE WHEN rec = 'Y' THEN 1 ELSE 0 END) >= 1 THEN 'Y' ELSE 'N' END "rec",
CASE  WHEN (SUM( CASE WHEN glob is null THEN 1 WHEN glob = Id THEN 2 ELSE 0 END ))  = 3 THEN 'Many'      
WHEN (SUM( CASE WHEN glob is null THEN 1 WHEN glob = Id THEN 2 ELSE 0 END ))  = 2  THEN 'One'       
ELSE 'None' END "Type"
FROM Table WHERE Id = '12345'
AND ((visible = 'Y' AND rec = 'N') OR rec = 'Y') AND days = 100
GROUP BY date, weeks_no, weeks_ind, weeks_rol
ORDER BY date, weeks_no;

And works well, as expected, returns all records. But if i add to GROUP BY the “rec” as

GROUP BY date, weeks_no, weeks_ind, weeks_rol, rec 

The query does not return all the records and more than that, the Type is wrong. Any ideas why is this happening? I know the group only groups the records and does not influence the number of records.

Answer

The rec in the GROUP BY is the one in the table, not the one in your query that’s been reduced to just ‘Y’/’N’.

You would need to put in an inner query, something like:

SELECT weeks_ind, weeks_rol, weeks_no, date,
CASE  WHEN (SUM( CASE WHEN glob is null THEN 1 WHEN glob = Id THEN 2 ELSE 0 END ))  = 3 THEN 'Many'      
WHEN (SUM( CASE WHEN glob is null THEN 1 WHEN glob = Id THEN 2 ELSE 0 END ))  = 2  THEN 'One'       
ELSE 'None' END "Type"
from (
  SELECT weeks_ind, weeks_rol, weeks_no, date,
  CASE WHEN SUM(CASE WHEN rec = 'Y' THEN 1 ELSE 0 END) >= 1 THEN 'Y' ELSE 'N' END "rec"
  FROM Table WHERE Id = '12345'
  AND ((visible = 'Y' AND rec = 'N') OR rec = 'Y') AND days = 100
  GROUP BY date, weeks_no, weeks_ind, weeks_rol
) group by date, weeks_no, weeks_ind, weeks_rol, rec
ORDER BY date, weeks_no;