Need SUM, AVG, COUNT of results GROUPED BY month and year only

sql-server tsql window-functions

360 观看

1回复

58 作者的声誉

I am trying to get Dollar SUM of Sales, Count of sales, Average Sale Price, Median No of Days on Market and Median Sale Price for each month of each year from the sales data through below mentioned query:

SELECT CAST(MNTH AS VARCHAR(2)) +'-'+ CAST(YR AS VARCHAR(4)) AS 'MONTH-YEAR', YR, MNTH, 
    MAX(dol_vol) AS 'Dol. Vol.',
    MAX(no_sales) AS '# of Sales',
    MAX(avg_price) AS 'Average Price',
    MAX(med_price) AS 'Median Price',
    MAX(med_days) AS 'Median Days'
FROM (SELECT YEAR(dt) YR, MONTH(dt) MNTH,
    SUM(sale_price) dol_vol,
    COUNT(sale_price) no_sales,
    AVG(sale_price) avg_price,
    PERCENTILE_CONT(.50) WITHIN GROUP (ORDER BY sale_price) OVER (PARTITION BY year(dt), month(dt)) med_price,
    PERCENTILE_CONT(.50) WITHIN GROUP (ORDER BY days_mkt) OVER (PARTITION BY year(dt), month(dt)) med_days
  FROM Sales_Data
  WHERE status='sld' AND year(dt)>='2000' AND sale_price>1000) d
GROUP BY YR, MNTH
ORDER BY YR, MNTH;

But, I am getting the below-mentioned error:

Msg 8120, Level 16, State 1, Line 7
Column 'Sales_Data.dt' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Now, if I try to add GROUP BY condition for 'dt' column in the sub-query, I get the same error for sale_price and days_mkt columns as well. How do I get res

作者: RAVI BANSAL 的来源 发布者: 2017 年 12 月 27 日

回应 1


1

23122 作者的声誉

决定

Try this:

    SELECT CAST(MNTH AS VARCHAR(2)) +'-'+ CAST(YR AS VARCHAR(4)) AS 'MONTH-YEAR', YR, MNTH, 
    MAX(dol_vol) AS 'Dol. Vol.',
    MAX(no_sales) AS '# of Sales',
    MAX(avg_price) AS 'Average Price',
    MAX(med_price) AS 'Median Price',
    MAX(med_days) AS 'Median Days'
FROM 
(
    SELECT YEAR(dt) YR, MONTH(dt) MNTH,
    NULL dol_vol,
    NULL no_sales,
    NULL avg_price,
    PERCENTILE_CONT(.50) WITHIN GROUP (ORDER BY sale_price) OVER (PARTITION BY year(dt), month(dt)) med_price,
    PERCENTILE_CONT(.50) WITHIN GROUP (ORDER BY days_mkt) OVER (PARTITION BY year(dt), month(dt)) med_days
    FROM Sales_Data
    WHERE status='sld' AND year(dt)>='2000' AND sale_price>1000

    UNION ALL

    SELECT YEAR(dt) YR, MONTH(dt) MNTH,
    SUM(sale_price) dol_vol,
    COUNT(sale_price) no_sales,
    AVG(sale_price) avg_price,
    NULL,
    NULL
    FROM Sales_Data
    WHERE status='sld' AND year(dt)>='2000' AND sale_price>1000
    GROUP BY  YEAR(dt)
             ,MONTH(dt) 

) d
GROUP BY YR, MNTH
ORDER BY YR, MNTH;

In the inner query we need exactly tell the engine by which columns/values to group by.

作者: gotqn 发布者: 2017 年 12 月 27 日
32x32