Summarising vs lifting values 1

[Naith]

Hi,

I am attempting to take data from a table and summarise it in part in a view. The reason for this is to denormalise the weekly rows over columns, replacing twelve weekly rows with one row with twelve weekly columns.

The relevant section of code looks like this:

...
sum(Case When replace(right(fweek,2),'.','') in (13,27,40,53) Then ts_adjd_qty End) as week13a,sum(bill_qty) as bill_qty,
sum(bill_amt_$) as bill_amt_$,
sum(ts_adjd_qty) as ts_adjd_qty,
sum(inv_rptd_qty) as inv_rptd_qty,
inv_demo_qty as inv_demo_qty,
inv_boh_qty as inv_boh_qty,
inv_coh_qty as inv_coh_qty,
inv_eoh_qty as inv_eoh_qty
FROM tbl

This excerpt focuses on the numeric fields, but as you can see, the fields starting with inv should not accumulate over weeks. Rather, I just want to take the latest value and populate these columns.

Using the above code, if I group by non aggregated columns, I end up inflating the total inv values.

e.g.

select sum(inv_boh_qty) from xxx where fiscal_week = 52: (167765)

select sum(inv_boh_qty) from summarised_xxx: (345203)

What should happen is that the sum non-summarised fields of the summarised table should reveal the same value as the original table.

Is there a simple way to achieve this? Because I'm missing what it is.

Thanks

 

[rac2]

Think of the problem in two parts, the aggregate using GROUP BY and the details as a JOIN of the aggregate query with the detail table.

The GROUP BY query should group by columns or expressions that can be used to JOIN to the detail table.

For example,

SELECT a.description, a.retail_price, b.SalesTotalWeek13
FROM Widgets a
JOIN (
       SELECT widget_id, SUM( actual_charge ) AS SalesTotalWeek13
       FROM Sales
       WHERE DATEPART(week, date_sold) = 13
       GROUP BY widget_id
     ) b ON b.widget_id = a.widget_id

 

[Naith]

Thanks rac, that's definitely a big step in the right direction.

The only problem is that I can't hardcode the week value, as this will be loaded several times a week, at various weeks in the quarter.

I'm coming from an Oracle perspective where I would ultimately say;

       SELECT widget_id, SUM( actual_charge ) AS SalesTotalWeek13
       FROM Sales
       WHERE fiscal_week = max(fiscal_week)

Wondering what the best way to capture this in sql server would be...

Thanks again

 

[rac2]

Try that in MS SQL Server and the result will be

Server: Msg 147, Level 15, State 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Which can be avoided by something like this.

SELECT widget_id, SUM( actual_charge ) AS SalesTotalRecentWeek
       FROM Sales
       WHERE fiscal_week = (
                SELECT max(fiscal_week)
                FROM Sales
               )
GROUP BY widget_id

 

[Naith]

Simple. Thanks rac2. Have a star on me.