CODEWITHSUNDEEP
sql-server
suhail
suhail

Reputation: 360

SQL Query to compare two rows under different condition

I need a query to retrieve the values from below table

enter image description here

I need a query to count total number of projects whose sum(total days) in the location 1 greater than sum(totaldays) in the location 2.

Please help me

Upvotes: 0

Views: 1821

Answers (3)

Andrey Gurinov
Andrey Gurinov

Reputation: 2885

Try this:

DECLARE @a TABLE (
    projectid INT,
    totalday INT, 
    location INT
)

INSERT @a (projectid, totalday, location)
VALUES  
    (12,         20,        2),
    (12,         10,        1),
    (13,         2,         1)

;WITH CTE AS (
    SELECT projectid, SUM(totalday) AS totalday, location FROM @a GROUP BY projectid, location 
)
SELECT COUNT(*)
FROM CTE AS a1
INNER JOIN CTE AS a2
    ON a1.projectid = a2.projectid
WHERE 
    a1.location = 1 AND a2.location = 2
    AND a1.totalday > a2.totalday

Upvotes: 2

Amit Singh
Amit Singh

Reputation: 8109

Select Count(T1.ProjectId) from (Select ProjectId ,Sum(TotalDay) as TL1 from Project where Location=1 gROUP BY ProjectId ) t1
inner Join
(Select ProjectId ,Sum(TotalDay) as Tl2 from Project where Location=2 grOUP BY ProjectId ) t2
on 
t1.ProjectId=T2.ProjectId  AND t1.tl1>t2.tl2

Sql Fiddle Demo

Upvotes: 2

StuartLC
StuartLC

Reputation: 107267

Assuming that there isn't a unique key constraint on your data, I've first summed up the total days per project per location with a CTE - if your data is unique, then you can skip this step.

WITH  ProjectDays AS
(
    SELECT projectid, location, SUM(totalday) AS TotalDays
    FROM Project p
    GROUP BY projectid, location
)
SELECT p1.projectId as ProjectWithMoreTotalDaysInLoc1
FROM ProjectDays p1 
    INNER JOIN ProjectDays p2
    ON p1.projectId = p2.projectId
WHERE 
    p1.location = 1 and p2.location = 2
    and p1.TotalDays > p2.TotalDays;

To simply get a count of the projects (rather than the list of projects themselves), just switch the final SELECT p1.projectId to SELECT COUNT(*)

Fiddle Here

Upvotes: 2

Related Questions