CODEWITHSUNDEEP
sql-serveraggregate-functions
Andrew
Andrew

Reputation: 2011

MSSQL Exclude primary key in group by

I have a query that looks like:

SELECT col1, col2, col3, col4, COUNT(*) OVER(PARTITION BY PAT07, PAT08, PAT18) AS TotalPerPerson
FROM Table1
GROUP BY col1, col2, col3, col4

It returns a table that looks like:

col1   col2   col3   col4   TotalPerPerson
data1  data2  data3  data4  2
data1  data2  data3  data5  2
data10 data11 data12 data13 3
data10 data11 data12 data14 3
data10 data11 data12 data15 3

The problem is that this query is in a with clause, and I have no way of joining this to the primary query. I need to select the "id" column in this query so I can use it in the inner join. How can I do that without adding the id to the group by?

Upvotes: 0

Views: 953

Answers (1)

liebs19
liebs19

Reputation: 549

You need to add it to the group by or an aggregate. You could try min() or max() or you could use a subquery to select a top 1. Without knowing how the primary key relates to this data it is hard to suggest the best answer.

For the subquery, try something like:

 SELECT 
 col1
,col2
,col3
,col4
,COUNT(*) OVER(PARTITION BY PAT07, PAT08, PAT18) AS TotalPerPerson
,(select top 1 id from Table1 where {criteria relating data to id}) as id 
FROM Table1 
GROUP BY 
 col1
,col2
,col3
,col4

Its hard to suggest the where clause as I'm not sure how this relates to the data you gave in the question

Upvotes: 1

Related Questions