locked
aggregate functions RRS feed

  • Question

  • I'm trying to get the query below to add an additional column that will give me the %s compared to the total of the "RoadCalls 07" column.  The query below works fine, but when I try to add the additional column I run into problems.  Any help would be appreciated..

    select wsshop
    as Shop,
    sum (case
          when wsrty like ('9%') and wsdateposted between 20060701 and 20070630
          then wstotalcost
          else 0 end) as 'RoadCalls 07',
    sum (case
           when wsrty not like ('9%') and wsdateposted between 20060701 and 20070630
           then wstotalcost
           else 0 end) as 'Non-RoadCalls 07'
    FROM faster.wsublet
    WHERE wsdateposted between 20040701 and 20080630
    GROUP BY wsshop
    order by wsshop

    I tried the below, but I can't get it to work.  Do I need to include aliases, and if so how should it be done... I'm totally frustrated...

    select
    wsshop as Shop,
    sum (case
            when wsrty like ('9%') and wsdateposted between 20060701 and 20070630
            then wstotalcost
            else 0 end) as 'RoadCalls 07',
    sum (case
          when wsrty not like ('9%') and wsdateposted between 20060701 and 20070630
          then wstotalcost
           else 0 end) as 'Non-RoadCalls 07',
    sum (case
         when wsrty like ('9%') and wsdateposted between 20060701 and 20070630
         then wstotalcost
          else 0 end)/(SELECT SUM(sum (case
                            when wsrty like ('9%') and wsdateposted between 20060701 and 20070630
                            then wstotalcost
                            else 0 end)))AS '% of Totals'
    FROM faster.wsublet
    WHERE wsdateposted between 20040701 and 20080630
    GROUP BY wsshop

    • Edited by reneelacks Wednesday, June 17, 2009 2:37 AM
    Wednesday, June 17, 2009 2:35 AM

Answers

  • Try these queries:

    SELECT
    wsshop AS Shop,
    SUM(CASE
            WHEN wsrty LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) AS [RoadCalls 07],
    SUM(CASE
            WHEN wsrty NOT LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) AS [Non-RoadCalls 07],
    SUM(CASE
            WHEN wsrty LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) * 1.0/
    MAX(total_non_road) AS [% of Totals]
    FROM (
    SELECT wsshop, wsrty, wstotalcost,
    SUM(CASE
            WHEN wsrty NOT LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) OVER() AS total_non_road
    FROM faster.wsublet
    WHERE wsdateposted BETWEEN 20060701 AND 20070630) AS T
    GROUP BY wsshop;

    SELECT
    wsshop AS Shop,
    SUM(CASE
            WHEN wsrty LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) AS [RoadCalls 07],
    SUM(CASE
            WHEN wsrty NOT LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) AS [Non-RoadCalls 07],
    SUM(CASE
            WHEN wsrty LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) * 1.0/
    (
    SELECT SUM(CASE
           WHEN wsrty NOT LIKE '9%'
           THEN wstotalcost
           ELSE 0 END)
    FROM faster.wsublet
     ) AS [% of Totals]
    FROM faster.wsublet
    WHERE wsdateposted BETWEEN 20060701 AND 20070630
    GROUP BY wsshop;

    SELECT
    wsshop AS Shop,
    SUM(CASE
            WHEN wsrty LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) AS [RoadCalls 07],
    SUM(CASE
            WHEN wsrty NOT LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) AS [Non-RoadCalls 07],
    SUM(CASE
            WHEN wsrty LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) * 1.0/
    SUM(CASE
            WHEN wsrty NOT LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) AS [% of Totals]
    FROM faster.wsublet
    WHERE wsdateposted BETWEEN 20060701 AND 20070630
    GROUP BY wsshop;


    -- Plamen Ratchev
    • Marked as answer by reneelacks Wednesday, June 17, 2009 6:29 PM
    Wednesday, June 17, 2009 2:14 PM

All replies

  • Try this:

    SELECT
    wsshop AS Shop,
    SUM(CASE
    WHEN wsrty LIKE '9%'
    AND wsdateposted BETWEEN 20060701 AND 20070630
    THEN wstotalcost
    ELSE 0 END) AS [RoadCalls 07],
    SUM(CASE
    WHEN wsrty NOT LIKE '9%'
    AND wsdateposted BETWEEN 20060701 AND 20070630
    THEN wstotalcost
    ELSE 0 END) AS [Non-RoadCalls 07],
    SUM(CASE
    WHEN wsrty LIKE '9%'
    AND wsdateposted BETWEEN 20060701 AND 20070630
    THEN wstotalcost
    ELSE 0 END) * 1.0/
    SUM(CASE
    WHEN wsdateposted BETWEEN 20060701 AND 20070630
    THEN wstotalcost
    ELSE 0 END) AS [% of Totals]
    FROM faster.wsublet
    WHERE wsdateposted BETWEEN 20040701 AND 20080630
    GROUP BY wsshop;

    BTW, it almost seems you just need to change the predicate for the date
    and then all expressions will be simplified:

    SELECT
    wsshop AS Shop,
    SUM(CASE
    WHEN wsrty LIKE '9%'
    THEN wstotalcost
    ELSE 0 END) AS [RoadCalls 07],
    SUM(CASE
    WHEN wsrty NOT LIKE '9%'
    THEN wstotalcost
    ELSE 0 END) AS [Non-RoadCalls 07],
    SUM(CASE
    WHEN wsrty LIKE '9%'
    THEN wstotalcost
    ELSE 0 END) * 1.0/
    SUM(wstotalcost) AS [% of Totals]
    FROM faster.wsublet
    WHERE wsdateposted BETWEEN 20060701 AND 20070630
    GROUP BY wsshop;

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    Wednesday, June 17, 2009 3:55 AM
  • this is close, but the column I need "summed" is the resulting column 'Non-RoadCalls 07', which of course it won't let me do.  See the adjustment to your query... or, in other words, if I added "with rollup" to my GROUP BY Clause, that's the figure I need to do my calculations on...

    SELECT
    wsshop AS Shop,
    SUM(CASE
    WHEN wsrty LIKE '9%'
    THEN wstotalcost
    ELSE 0 END) AS [RoadCalls 07],
    SUM(CASE
    WHEN wsrty NOT LIKE '9%'
    THEN wstotalcost
    ELSE 0 END) AS [Non-RoadCalls 07],
    SUM(CASE
    WHEN wsrty LIKE '9%'
    THEN wstotalcost
    ELSE 0 END) * 1.0/
    SUM(Non-RoadCalls 07) AS [% of Totals]
    FROM faster.wsublet
    WHERE wsdateposted BETWEEN 20060701 AND 20070630
    GROUP BY wsshop with rollup;

                                              Non-               % of                                       % of
    SHOP    RoadCalls07     RoadCalls07     Totals                                      Totals
    NULL     433819.72          5913842.51       0.931656                                   1.00000
    ADM           845.00             29094.27       0.971776        should be             0.004920
    BOD         5721.93         1460446.15        0.996097                                  0.246954
    BRV           180.00             35837.84        0.995002                                  0.006060   etc.
    CAR         1732.00           197718.04        0.991316
    CVT          1050.00           359478.19        0.997087
    FIR           6984.89           570667.61        0.987908
    FWY          4640.88           322781.21        0.985826
    HEQ        25574.03           285495.91        0.917786
    LIB                0.00             39738.21        1.000000
    LWN         1720.00           292318.88         0.994150
    MEC           125.00            31374.17         0.996031
    PRD           525.00             17696.50        0.971187
    PUL            881.00           476727.03        0.998155
    RPR          1433.22           484383.49        0.997049
    TIR       239683.91           103346.68        0.301275
    TRK      142612.86          1041286.74        0.879539
    YRK           110.00            165451.59        0.999335

    Wednesday, June 17, 2009 7:20 AM
  • Hi Rene, Plaman,

    Have you heard about the Group By Grouping Sets  a T-SQL improvement with MS SQL Server 2008

    Group By Grouping Sets gives more control over grouping and aggregation during select statements.

    I think it worths a quick look.

    Eralper
    T-SQL and SQL Server 2008

    http://www.kodyaz.com http://www.eralper.com
    Wednesday, June 17, 2009 7:31 AM
  • You could get that by using a CTE:

    WITH cte AS
    (
      SELECT wsshop AS Shop
            ,SUM(CASE WHEN wsrty LIKE '9%' THEN wstotalcost ELSE 0 END) AS [RoadCalls 07]
            ,SUM(CASE WHEN wsrty NOT LIKE '9%' THEN wstotalcost ELSE 0 END) AS [Non-RoadCalls 07],
      FROM faster.wsublet
      WHERE wsdateposted BETWEEN 20060701 AND 20070630
      GROUP BY wsshop with rollup
    )
    SELECT wsshop
          ,[RoadCalls 07]
          ,[Non-RoadCalls 07]
          ,[Non-RoadCalls 07]/SUM(CASE WHEN wsshop IS NULL THEN 0 ELSE [Non-RoadCalls 07] END) OVER () AS [% of Totals]
    FROM cte;
    

    --Brad
    Wednesday, June 17, 2009 1:05 PM
  • Yes, but grouping sets do not help to solve this problem.

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    Wednesday, June 17, 2009 2:04 PM
  • Try these queries:

    SELECT
    wsshop AS Shop,
    SUM(CASE
            WHEN wsrty LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) AS [RoadCalls 07],
    SUM(CASE
            WHEN wsrty NOT LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) AS [Non-RoadCalls 07],
    SUM(CASE
            WHEN wsrty LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) * 1.0/
    MAX(total_non_road) AS [% of Totals]
    FROM (
    SELECT wsshop, wsrty, wstotalcost,
    SUM(CASE
            WHEN wsrty NOT LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) OVER() AS total_non_road
    FROM faster.wsublet
    WHERE wsdateposted BETWEEN 20060701 AND 20070630) AS T
    GROUP BY wsshop;

    SELECT
    wsshop AS Shop,
    SUM(CASE
            WHEN wsrty LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) AS [RoadCalls 07],
    SUM(CASE
            WHEN wsrty NOT LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) AS [Non-RoadCalls 07],
    SUM(CASE
            WHEN wsrty LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) * 1.0/
    (
    SELECT SUM(CASE
           WHEN wsrty NOT LIKE '9%'
           THEN wstotalcost
           ELSE 0 END)
    FROM faster.wsublet
     ) AS [% of Totals]
    FROM faster.wsublet
    WHERE wsdateposted BETWEEN 20060701 AND 20070630
    GROUP BY wsshop;

    SELECT
    wsshop AS Shop,
    SUM(CASE
            WHEN wsrty LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) AS [RoadCalls 07],
    SUM(CASE
            WHEN wsrty NOT LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) AS [Non-RoadCalls 07],
    SUM(CASE
            WHEN wsrty LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) * 1.0/
    SUM(CASE
            WHEN wsrty NOT LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) AS [% of Totals]
    FROM faster.wsublet
    WHERE wsdateposted BETWEEN 20060701 AND 20070630
    GROUP BY wsshop;


    -- Plamen Ratchev
    • Marked as answer by reneelacks Wednesday, June 17, 2009 6:29 PM
    Wednesday, June 17, 2009 2:14 PM
  • Plamen... can't thank you enough.  This one below worked like a charm.  If not too much trouble, can you explain what the query is doing, expecially confused by the "OVER"..... and why Aias "T"

    and thanks for everyone's input

    SELECT
    wsshop AS Shop,
    SUM(CASE
            WHEN wsrty LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) AS [RoadCalls 07],
    SUM(CASE
            WHEN wsrty NOT LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) AS [Non-RoadCalls 07],
    SUM(CASE
            WHEN wsrty LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) * 1.0/
    MAX(total_non_road) AS [% of Totals]
    FROM (
    SELECT wsshop, wsrty, wstotalcost,
    SUM(CASE
            WHEN wsrty NOT LIKE '9%'
            THEN wstotalcost
            ELSE 0 END) OVER() AS total_non_road
    FROM faster.wsublet
    WHERE wsdateposted BETWEEN 20060701 AND 20070630) AS T
    GROUP BY wsshop;
    • Edited by reneelacks Wednesday, June 17, 2009 6:38 PM
    Wednesday, June 17, 2009 6:15 PM
  • Only on 2005
    Wednesday, June 17, 2009 6:33 PM
  • Hate to sound so stupid, but I'm pretty new to SQL... CTE???  Totally lost, how does it work?
    Wednesday, June 17, 2009 6:35 PM
  • CTE means "Common Table Expression" (only available in SQL2005 and above).

    It's like a temporary result set (like a derived table), and one can make an argument that its syntax is more clear

    In my example, it's equivalent to writing the following:

    SELECT wsshop
          ,[RoadCalls 07]
          ,[Non-RoadCalls 07]
          ,[Non-RoadCalls 07]/SUM(CASE WHEN wsshop IS NULL THEN 0 ELSE [Non-RoadCalls 07] END) OVER () AS [% of Totals]
    FROM (SELECT wsshop AS Shop
                ,SUM(CASE WHEN wsrty LIKE '9%' THEN wstotalcost ELSE 0 END) AS [RoadCalls 07]
                ,SUM(CASE WHEN wsrty NOT LIKE '9%' THEN wstotalcost ELSE 0 END) AS [Non-RoadCalls 07],
          FROM faster.wsublet
          WHERE wsdateposted BETWEEN 20060701 AND 20070630
          GROUP BY wsshop with rollup) AS cte
    


    It especially comes in handy when you don't want to repeat the same sub-query multiple times.  In the code I posted for you, the CTE was only referenced once.  But it can be referenced multiple times in the main query, eliminating a lot of duplicate code.


    --Brad
    Wednesday, June 17, 2009 7:11 PM
  • First, the subquery in the derived table simply calculates the total non
    road cost for each row. The trick here is using an aggregate SUM
    function with the OVER clause. That allows you to calculate any
    aggregate based on partition. Normally you would use it like
    SUM(columnA) OVER(PARTITION BY columnB). But in this case you need the
    total amount, so you can skip the PARTITION BY clause and simply use
    OVER(). You can read more about OVER here
    http://msdn.microsoft.com/en-us/library/ms189461.aspx?ppud=4.

    SELECT wsshop, wsrty, wstotalcost,
    SUM(CASE
    WHEN wsrty NOT LIKE '9%'
    THEN wstotalcost
    ELSE 0 END) OVER() AS total_non_road
    FROM faster.wsublet
    WHERE wsdateposted BETWEEN 20060701 AND 20070630

    All derived tables have to be aliased, so this is why the alias T.

    The next step is simply to to calculate the summary columns (which you
    already had correct) and to divide by the total non road calculated in
    the derived table. Also, here you have to use MAX (or MIN, it doesn't
    matter since the value is the same for all rows) to get the value (or
    have to include it in the GROUP BY clause).

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    Wednesday, June 17, 2009 8:28 PM