March 8, 2016 at 3:14 pm
MMartin1 (3/8/2016)
Going deeper with the CROSS APPLY suggestion :Consider something like this >
SELECT SUM(someNumberColumn) / (SELECT SUM(someNumberColumn) FROM Table) AS westCoastSalesAsA%OfTotal
FROM Table
WHERE someFilterColumn= 'west coast'
Could be done with something like ( I didnt totally check my syntax but the idea is )
SELECT SUM(t1.someNumberColumn) / (t2.totalSales) AS westCoastSalesAsA%OfTotal
FROM Table t1 CROSS APPLY
(SELECT SUM(someNumberColumn) as totalSales FROM Table) as t2
WHERE t1.someFilterColumn= 'west coast'
It appears cleaner.
It's probably going to be more efficient to do the following, because it only requires one scan of the table.
SELECT SUM(CASE WHEN t1.someFilterColumn = 'West Coast' THEN t1.someNumberColumn END) / SUM(t1.someNumberColumn)
FROM Table t1
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 8, 2016 at 3:41 pm
drew.allen (3/8/2016)
MMartin1 (3/8/2016)
Going deeper with the CROSS APPLY suggestion :Consider something like this >
SELECT SUM(someNumberColumn) / (SELECT SUM(someNumberColumn) FROM Table) AS westCoastSalesAsA%OfTotal
FROM Table
WHERE someFilterColumn= 'west coast'
Could be done with something like ( I didnt totally check my syntax but the idea is )
SELECT SUM(t1.someNumberColumn) / (t2.totalSales) AS westCoastSalesAsA%OfTotal
FROM Table t1 CROSS APPLY
(SELECT SUM(someNumberColumn) as totalSales FROM Table) as t2
WHERE t1.someFilterColumn= 'west coast'
It appears cleaner.
It's probably going to be more efficient to do the following, because it only requires one scan of the table.
SELECT SUM(CASE WHEN t1.someFilterColumn = 'West Coast' THEN t1.someNumberColumn END) / SUM(t1.someNumberColumn)
FROM Table t1
Drew
I would do it that way also. This is just a quick scenario example I thought up (hence why I mentioned could be done this way) to illustrate programming within the SELECT statement that could be done with a set based approach using CROSS APPLY as had been suggested. It doesnt apply perfectly to my example as the best way... just a way.
----------------------------------------------------
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply