July 25, 2012 at 12:55 pm
I have a query like this:
select
x.Delay,
x.Cancel,
RentalPeriod
from ( Select RentalPeriod
Delay = (select COUNT(delay) as D from RentalAgreements where delay = 'Y' ),
Cancel = (select COUNT(CANCELATION) as C from RentalAgreements where CANCELATION = 'Y' )
from RentalAgreements
) as x
Group by Delay, RentalPeriod Period
how can I get a count for each rentalPeriod? When I run this query, I see all rental months however, I see the same count for each rental period like this:
Delay Cancel Period
2911 301 201001
2911 301 201002
2911 301 201003
2911 301 201004
2911 301 201005
2911 301 201006
2911 301 201007
2911 301 201008
2911 301 201012
how can I see it as
period delay cancel
201001 2911 301
201002 2800 550
and so on?
July 25, 2012 at 1:07 pm
With nothing to test against, try either of these:
select
x.Delay,
x.Cancel,
RentalPeriod
from
( Select RentalPeriod,
Delay = (select COUNT(r2.delay) as D from RentalAgreements r2 where r2.delay = 'Y' and r2.RentalPeriod = r1.RentalPeriod ),
Cancel = (select COUNT(r3.CANCELATION) as C from RentalAgreements r3 where r3.CANCELATION = 'Y' and r3.RentalPeriod = r1.RentalPeriod )
from RentalAgreements r1
) as x
order by
RentalPeriod;
select
RentalPeriod,
sum(case delay when 'Y' then 1 else 0 end) as DelayCnt,
sum(case cancellation when 'Y' then 1 else 0 end) as CancellationCnt
from
RentalAgreement
group by
RentalPeriod;
July 25, 2012 at 1:26 pm
Its returning me 0 for my counts
July 25, 2012 at 1:28 pm
I'm curious Lynn,
You presented the first solution just because it would be a correction of the original query?
Because it seems to me that it would give terrible performance compared to the second solution.
July 25, 2012 at 1:32 pm
SQL_NuB (7/25/2012)
Its returning me 0 for my counts
You should post DDL and sample data, so we can compare results.
Added: Also post the query that returned 0 for the counts.
July 25, 2012 at 1:42 pm
SQL_NuB (7/25/2012)
Its returning me 0 for my counts
And you'd like me to do what? You didn't provide any DDL for your tables, nor sample data with which to load those tables, nor the expected results based on the sample data.
I can't vouch for the correctness of my code if I have nothing to test it against.
July 25, 2012 at 1:46 pm
Luis Cazares (7/25/2012)
I'm curious Lynn,You presented the first solution just because it would be a correction of the original query?
Because it seems to me that it would give terrible performance compared to the second solution.
Yes, the first was to make what I thought were appropriate corrections to the OP's original code which did not limit the data being aggregated in the subqueries to the period being selected.
And yes, I would think that the second solution should provide a better solution.
Unfortunately, it appears they don't work.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply