programming within SELECT statement

Question

programming within SELECT statement

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76776 More actions · Answer 1

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

MMartin1 One Orange Chip Points: 27655 More actions · Answer 2

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.

----------------------------------------------------