programming within SELECT statement

  • 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

  • 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