CODEWITHSUNDEEP
sqlsql-server
CodeTester9001
CodeTester9001

Reputation: 47

SQL Different Group By For New Column

I've tried every search under the sun to try and solve this, either that or I'm not searching properly

I've got a query with a sub query, with 2 union'd queries inside

Example:-

SELECT name, status , SUM(TestCount) as ‘TestCount’
FROM(
select e1.reference as reference, 
e1.name as name,
e1.status as status,
, SUM(CASE WHEN e1.date IS NOT NULL THEN 1 ELSE 0 END) as TestCount
FROM dbo.table1 as e1


WHERE e1.status IN('A','B','C')
GROUP BY e1.name, e1.status

UNION ALL

select e2.reference as reference, 
e2.name as name,
e2.status as status,
SUM(CASE WHEN e2.date IS NOT NULL THEN 1 ELSE 0 END) as TestCount
FROM dbo.table2 as e2


WHERE e2.status IN('A','B','C')
GROUP BY e2.name, e2.status

) t

GROUP BY Name, Status

I want to add a SUM CASE WHEN column to my sub queries, then pull it out at the top, to only group by name, not status. (So if a person's name showed up 5 times for 5 different status', just show the same number for their name 5 times). Problem is, if I use a WHERE clause (either inside) or HAVING outside, it filters out the status' that have the SUM CASE WHEN criteria of the last column

For example

Status:-

A B C D E - HERE IS WHERE THE SUM CASE WHEN COMES FROM

I want to use a WHERE to only show A , B , C , D but I want a count for E..

Hope this makes sense

Edited as per suggestion:-

SUM(CASE WHEN MONTH(Field1Date) = MONTH(GETDATE()) AND YEAR(Field1Date) = YEAR(GETDATE())  AND TypeCode IN('A', 'B', 'C')  THEN 1
 ELSE 0 END) OVER (PARTITION BY Name)  as full_count

This then causes:-

Msg 8120, Level 16, State 1, Line 1
Column 't.Field1Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 't.Field1Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 't.TypeCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 't.TypeCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 't.TypeCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Doing this then reduces the error to 2:-

SUM(CASE WHEN MONTH(Field1Date) = MONTH(GETDATE()) AND YEAR(Field1Date) = YEAR(GETDATE())  AND TypeCode IN('A', 'B', 'C')  THEN 1
 ELSE 0 END) OVER (PARTITION BY Name, Field1Date, TypeCode)  as full_count

Msg 8120, Level 16, State 1, Line 2
Column 't.Field1Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column 't.TypeCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I can get rid of the last 2 errors by adding them to the GROUP BY, but I didn't want to GROUP BY them to begin with if that makes sense?

If I add them to the GROUP BY to overcome the error, the SUM is always 0 because the WHERE statements' in the inner queries, filter out all 'status' where the count would reside

Sorry, I should say this is SQL Server. TSQL in SSMS

Upvotes: 0

Views: 82

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You are probably looking for SUM OVER to get a total over a group. You haven't told us which DBMS you are using, but this is a standard SQL function available in many DBMS.

I've reduced your SUM(CASE WHEN date IS NOT NULL THEN 1 ELSE 0 END) to simply COUNT(date). I've also removed the aggregations from the inner queries, as you must aggregate in the main query anyway.

SELECT
  name,
  status,
  COUNT(date) as partial_count,
  SUM(COUNT(date)) OVER (PARTITION BY name) as full_count
FROM
(
  SELECT name, status, date FROM dbo.table1 WHERE status IN ('A', 'B', 'C')
  UNION ALL
  SELECT name, status, date FROM dbo.table2 WHERE status IN ('A', 'B', 'C')
) t
GROUP BY name, status
ORDER BY name, status;

Upvotes: 1

Related Questions