Nesting or Joining Percentile, AVG, MIN, MAX aggregation queries grouped by month and year

Transact-SQL
1

Greetings: I have written 2 different aggregation queries that both work well separately

Query 1: SUM, MIN, MAX, AVG grouped by DateName (month) and Year.

Query 2: Median using Percentile_Disc, also grouped by DateName (month) and Year.

I am having trouble nesting the queries and wonder what would the best way be to do this.

Query 1:


    SELECT 
      SUM(DailyDiff) GateCounts_MonthlyTotal,
      MIN(DailyDiff)  GateCounts_MIN,
      MAX(DailyDiff) GateCounts_MAX, 
      AVG(DailyDiff) GateCounts_AVG,
      DATENAME(month,dateadd(month,DATEPART(month,[Date]),-1)) Month_Name,
      Month([Date])  GateCounts_Month,
      YEAR([Date]) Year_name

    FROM GateCounts gc

    WHERE [Date] BETWEEN  '8/24/2015 12:00:00 AM' AND '12/30/2015 12:00:00 AM'
    GROUP BY  Year([Date]), Month([Date])
    ORDER BY Year([Date])DESC, GateCounts_Month DESC;

Query 2:

SELECT 
DailyDiff,
GateCounts_MED = PERCENTILE_DISC(0.5) 
WITHIN GROUP (ORDER BY DailyDiff DESC) 
OVER (PARTITION BY DATEPART(month, [Date])),
  DATENAME(month,dateadd(month,DATEPART(month,[Date]),-1)) Month_Name,
  Month([Date])  GateCounts_Month,
  YEAR([Date]) Year_name

FROM GateCounts gc
WHERE [Date] BETWEEN  '8/24/2015 12:00:00 AM' AND '12/30/2015 12:00:00 AM'

GROUP BY DailyDiff, Year([Date]), Month([Date])
ORDER BY Year([Date])DESC, GateCounts_Month DESC;
2

Not having sample data to "play" with, the following query might not get you over the finishingline. But give it a go and let us now how it went.
When filtering on datetime you should never use between statement. You should rather do "[date]>=startdate and [date]<enddate+1".
Also you should use iso standard when writing date (YYYY-MM-DD).

select sum(dailydiff) as gatecounts_monthlytotal
      ,min(dailydiff) as gatecounts_min
      ,max(dailydiff) as gatecounts_max
      ,avg(dailydiff) as gatecounts_avg
      ,percentile_disc(.5)
           within group (order by dailydiff desc)
           over (partition by month([date]))
       as gatecount_med
      ,datename(month,[Date]) as gatecount_month_name
      ,month([date]) as gatecount_month
      ,year([date]) as gatecount_year
  from gatecounts
 where [date]>=cast('2015-08-24 00:00:00' as datetime)
   and [date]<cast('2015-12-30 00:00:00' as datetime)
 group by year([date])
         ,month([date])
         ,datename(month,[date])
 order by gatecount_year
         ,gatecount_month desc
;
1 Like
4

Greetings! I figured out how to obtain Median and include it as a nested query.

        SELECT
        Year([Date]) GC_Year, 
          Month([Date]) GC_Month, 
          DATENAME(month,[Date]) Month_Name,
          SUM(DailyDiff) AS GateCounts_MonthlyTotal,
          MIN(DailyDiff) AS GateCounts_MIN,
          MAX(DailyDiff) AS GateCounts_MAX, 
          AVG(DailyDiff) AS  GateCounts_AVG,
          GateCounts_MED=MAX(Median)

        FROM
        (
           SELECT [Date], DailyDiff,
            Median=PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY DailyDiff) OVER (PARTITION BY CAST(MONTH([Date]) AS VARCHAR(2)) + CAST(YEAR([Date]) AS VARCHAR(4)))
           FROM GateCounts 
        ) a

    WHERE [date]>=cast('2015-08-24 00:00:00' as datetime)
    AND [date]<cast('2015-12-30 00:00:00' as datetime)

    GROUP BY  Year([Date]), Month([Date]), DATENAME(month,[Date])

    ORDER BY GC_Year DESC, GC_Month DESC;

Thank you very much for your guidance.
I hope this will help others. I can supply insert and dummy values if you like to test. Thank you .