CODEWITHSUNDEEP
sql-serverdatabasegroup-by
Azzurro94
Azzurro94

Reputation: 499

Two average with one group by in SQL Server

I have two group by and average queries, i need to get both result together. I mean assume i have two queries like this:

select timespan, count(*) c1
from dbo 
where C > 50
group by timespan
having count(*) > 100

and

select timespan, count(*) c2
from dbo 
where C < 50
group by timespan
having count(*) > 130

EDITED I need to have both next to each other

  TimeSpan, c1, c2
    630,  120, 100
    631,  140, 120

This is my query in SQL Server, but it doesn't work:

select 
    gt, ls 
from  
    (select count (*) gt 
     from dbo.Alltrips 
     where DrivingTime > 50  
       and CarId in (select CarId from dbo.Alltrips 
                     group by CarId 
                     having Count (*) > 100) 
     group by timeSpan) a,
    (select count (*) ls 
     from dbo.Alltrips 
     where DrivingTime < 50  
       and CarId in (select CarId from dbo.Alltrips 
                     group by CarId 
                     having Count (*) > 130) 
     group by timeSpan) b

Upvotes: 0

Views: 51

Answers (1)

Thom A
Thom A

Reputation: 95561

Use conditional aggregation:

SELECT timespan,
       COUNT(CASE WHEN C > 100 THEN 1 END) AS C1,
       COUNT(CASE WHEN C > 130 THEN 1 END) AS C2
FROM dbo.dbo --Do you really have a table called dbo?
GROUP BY timespan
HAVING COUNT(CASE WHEN C > 100 THEN 1 END) > 100;

Upvotes: 1

Related Questions