CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Ramesh Sivaraman
Ramesh Sivaraman

Reputation: 1305

Aggregation result with a unique column in SQL Server

I performing an aggregation on a table, below is a snap shot of the Job table I am querying.

enter image description here

my query is similar to below. In reality the above derived table will have many more columns as results of multiple joins.

select Attribute   
from Job 
where Description = 'Installation' and Attribute = 'NPL' 
group by Attribute 
having sum(Cost) >= 500

I want to get the JobID of the matching records as that is unique and I can use it for further joins. How can I get the jobID's(which is PK) of the matching records.

I am not sure if its OK to do this,

select Attribute, JobID 
from Job 
where Description = 'Installation' and Attribute = 'NPL' 
group by Attribute, JobID  
having sum(Cost) >= 500

I am expecting the result like(for above scenario),

enter image description here

Upvotes: 0

Views: 77

Answers (3)

Aleksei Semidotskii
Aleksei Semidotskii

Reputation: 1465

Try this

    SELECT 
        J.JobId,
        J.Description 
    FROM dbo.Jobs J
    INNER JOIN
    (
        SELECT 
            Attribute
        FROM dbo.Jobs
        GROUP BY
            Attribute
        having 
            sum(Cost) >= 500
    ) AS G
    ON J.Attribute = G.Attribute 
    where 
        J.Description = 'Installation' 
        and J.Attribute = 'NPL'

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

You can use aggregates with window functions so that the original rows are preserved but you can reason about the aggregate value. You need to compute the aggregate in a subquery or CTE though:

;With Totals as (
    select *,SUM(Cost) OVER (PARTITION BY Attribute) as TotalCost
    from Job
    where Description = 'Installation' and Attribute = 'NPL' 
)
select * from Totals where TotalCost > 500

Upvotes: 3

har07
har07

Reputation: 89325

Grouping this way won't give you desired result :

Group by Attribute, JobID

with that, JobID = 34 and JobID = 39 will be in separate group. One of several possible ways is using INNER JOIN and subquery to achieve that "expected result" picture :

select j.Attribute, j.JobID   
from Job j
    inner join
        (select Attribute   
         from Job 
         where Description = 'Installation' and j.Attribute = 'NPL' 
         group by Attribute 
         having sum(Cost) >= 500) a on a.Attribute = j.Attribute
where j.Description = 'Installation' and j.Attribute = 'NPL'

Upvotes: 1

Related Questions