Results 1 to 5 of 5
  1. #1
    Join Date
    May 2012
    Posts
    17

    Unanswered: Optimize my SQL statement

    The following SQL statement works fine, it involves a single table. The aim of the 8 subqueries is to determine subtotals at various levels.
    Just wondering if this statement can be streamlined/optimized to run better/faster or should I leave it as is.

    SELECT Groupnbr AS Groupnbr, Subgrp AS Subgrp, SECTION AS Section,
    (SELECT Sum(Sales-Exp) from BlueTable where subgrp IN ('A','B')) as SubTot1,
    (SELECT Sum(Sales-Exp) from BlueTable where subgrp IN ('C','D')) as SubTot2,
    (SELECT Sum(Sales-Exp) from BlueTable where subgrp IN ('C','D','E')) as SubTot3,
    (SELECT Sum(Sales-Exp) from BlueTable where subgrp IN ('F','G')) as SubTot4,
    (SELECT Sum(Sales-Exp) from BlueTable where subgrp IN ('F','G','H')) as SubTot5,
    (SELECT Sum(Sales-Exp) from BlueTable where subgrp IN ('K','L')) as SubTot6,
    (SELECT Sum(Sales-Exp) from BlueTable where subgrp IN ('M','P')) as SubTot7,
    (SELECT Sum(Sales-Exp) from BlueTable where subgrp IN ('M','P','Q')) as SubTot8
    from BlueTable

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what evidence have you got that it isn't performing reasonably?

    if you do have a problem then you may be better off doing your final summation in the front end (the presentation layer)

    SELECT sum(Sales-EXP) AS GroupNbr, Subgrp, Section
    FROM BlueTable
    GROUP BY Subgrp
    ORDER BY Subgrp;

    then do you addition in whatever the front end
    But there may well be a smarter approach from one of the experts on SQL
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2012
    Posts
    17
    I have no performance problem at the moment (the DB has barely 200 records, just started being used). Being a newbie I am not sure if this is the best or good way to go about it, but how about when it reaches a few hundred thousand records? I read somewhere that it's better to use Joins than subquerries (?), but have no clue how to replace each one of these subqueries with a Join. If my subquerries might pose a performance problem in the future and there is a better approach, please suggest. Thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Groupnbr
         , SECTION AS Section
         , SUM(CASE WHEN subgrp IN ('A','B')
                    THEN Sales - Exp
                    ELSE NULL END) as SubTot1
         , SUM(CASE WHEN subgrp IN ('C','D')
                    THEN Sales - Exp
                    ELSE NULL END) as SubTot2
         , SUM(CASE WHEN subgrp IN ('C','D','E')
                    THEN Sales - Exp
                    ELSE NULL END) as SubTot3
         , SUM(CASE WHEN subgrp IN ('F','G')
                    THEN Sales - Exp
                    ELSE NULL END) as SubTot4
         , SUM(CASE WHEN subgrp IN ('F','G','H')
                    THEN Sales - Exp
                    ELSE NULL END) as SubTot5
         , SUM(CASE WHEN subgrp IN ('K','L')
                    THEN Sales - Exp
                    ELSE NULL END) as SubTot6
         , SUM(CASE WHEN subgrp IN ('M','P')
                    THEN Sales - Exp
                    ELSE NULL END) as SubTot7
         , SUM(CASE WHEN subgrp IN ('M','P','Q')
                    THEN Sales - Exp
                    ELSE NULL END) as SubTot8
      FROM BlueTable
    GROUP
        BY Groupnbr
         , SECTION
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2012
    Posts
    17
    Thanks Rudy.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •