CODEWITHSUNDEEP
sql-servercountsubquerydivision
user3892732
user3892732

Reputation: 13

Count and divide subquery: SQL Server

Here is a table I am reading:

sk_calendar_week  | ClientId  | Amount 
------------------+-----------+-------
2014001           | 1         | 550
2014002           | 2         | 900
2014003           | 3         | 389
2014004           | 4         | 300

Here is the query I'm using:

declare @IniDate as int = 20140610, @EndDate as int = 20150425

select   
    COUNT(distinct sk_calendar_week) WeekQ, 
    COUNT(distinct sk_calendar_Month) MonthQ
from
    (select   
         sk_date, sk_calendar_week, sk_calendar_Month, 
         ClientId, Amount
     from 
         TableA
     where 
         Sk_Date between @IniDate and @EndDate) q1

This query returns:

WeekQ | MonthQ
------+-------
4     | 1

How can I divide the 4 from WeekQ with the Amount (550/4; 900/4; 389/4...), to obtain a result like this?

sk_calendar_week   | ClientId | Amount | Division
-------------------+----------+--------+---------
2014001            | 1        | 550    | 137.5
2014002            | 2        | 900    | 225
2014003            | 3        | 389    | 97.25
2014004            | 4        | 300    | 75

Upvotes: 0

Views: 6466

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

You can use your first query to populate a local variable and the use it in the second query like this:

declare @IniDate as int = 20140610, 
              @EndDate as int = 20150425,
              @Week int

select @Week = COUNT(distinct sk_calendar_week) 
from TableA where Sk_Date between @IniDate and @EndDate )

Select sk_calendar_week,
            ClientId,
            Amount,
            cast(Amount as decimal(8,2)) / @Week as Divsion

A sub query version would suffer a performance hit, but here is an example:

Select sk_calendar_week,
            ClientId,
            Amount,
            cast(Amount as decimal(8,2)) / 
            (select COUNT(distinct sk_calendar_week) 
             from TableA where Sk_Date between @IniDate and @EndDate ) as Divsion

Upvotes: 2

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Try with window function:

declare @IniDate as int = 20140610, @EndDate as int = 20150425

select *, amount*1.0/count(*) over() as division
from(
    select   sk_date
            ,sk_calendar_week
            ,sk_calendar_Month
            ,ClientId
            ,Amount
    from TableA
    where Sk_Date between @IniDate and @EndDate
)q1

Upvotes: 1

Related Questions