locked
Sum function within other sum function? RRS feed

  • Question

  • Hi there,

    i am trying to frame a SQl query and my expected output is something like this,

    Val1 Val2 Per1 Per2
    Management 5164 9131 11.43 13
    Business and Financial Operations 4257 6539 9.42 9.31
    Computer and Mathematical 11167 17303 24.71 24.64


    And the query that i have tried is,

    SELECT TOP 200 OC.OccFamilyCode, OC.OccFamilyName

    ,SUM (case when c.date between '2011-11-01' and '2011-11-15' then J.Canonnumberofopenings else 0 end) as CDCOUNT

    ,SUM (case when c.date between '2011-11-15' and '2011-11-30' then J.Canonnumberofopenings else 0 end) as PDCOUNT

    ,0

    ,Round((SUM (case when c.date between '2011-11-01' and '2011-11-15' then J.Canonnumberofopenings end)*10000)/Convert(Float,8354800),2)

    ,Round((SUM (case when c.date between '2011-11-15' and '2011-11-30' then J.Canonnumberofopenings end)*10000)/Convert(Float,4424300),2)

    from dw_vw_Job J

    inner join vw_CanonJobLocation VCJL on VCJL.ID = J.CanonJobLocationID 

    inner join Calendar C (NOLOCK) on C.ID = J.JobDateID

    --inner join JobsFT JFT (NOLOCK) on JFT.JobID = J.JobID

    inner join dw_OnetCode OC (NOLOCK) on OC.ID=J.OnetID 

    Where (( VCJL.CanonCountryID = 6 ) and  ( VCJL.State = 'CA'  )

     ANDOC.ID NOTIN (1,1392))GROUPBYOC.OccFamilyCode,OC.OccFamilyName

      ORDERBYSUM(J.CANONNUMBEROFOPENINGS)DESC

    I dont want to hard code this in the query

    --Convert(Float,8354800),2)

    which is nothing but the sum of the column Val1 (i.e SUM (case when c.date between '2011-11-01' and '2011-11-15' then J.Canonnumberofopenings end))

    I am not allowed to do sum upon other sum function, how can i do this?

    Can anyone help me out with this?

    Thanks,

    Aish



    Friday, March 2, 2012 1:12 PM

Answers

  • Hi all,

    I myself explored and got this done in other way like this,

    Convert(decimal(5,2),SUM (case when c.date between '2010-11-1' and '2010-11-30' then J.Canonnumberofopenings else 0 end)*100.0 /(Select Sum(J1.CanonNumberOfOpenings) from dw_Job J1 inner join Calendar C1 on C1.ID=J1.JobDateID where c1.date between '2010-11-1' and '2010-11-30')) as Per1

    ,Convert(decimal(5,2),SUM (case when c.date between '2011-11-1' and '2011-11-30' then J.Canonnumberofopenings else 0 end)*100.0  /(Select Sum(J1.CanonNumberOfOpenings) from dw_Job J1 inner join Calendar C1 on C1.ID=J1.JobDateID where c1.date between '2011-11-1' and '2011-11-30')) as Per2 

    Thanks,

    Aish


    • Marked as answer by IshNair Monday, March 5, 2012 9:33 AM
    Monday, March 5, 2012 9:33 AM

All replies

  • You can construct an outer query for the additonal processing:

    SELECT .......

    FROM ( YOUR CURRENT QUERY ) x

    ORDER BY ....


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES

    Friday, March 2, 2012 1:29 PM
  • Please post ddl, so that people do not have to guess what the keys, constraints, declarative referential integrity, data types, etC. in your schema are. If you know how, follow iso-11179 data element naming conventions and formatting rules. Temporal data should use iso-8601 formats. Code should be in standard SQL AS much AS possible and not local dialect. This is minimal polite behavior on a SQL forum.


    We do not use FLOAT; the rounding problem are not worth it. The word “date” is both a reserved word and too vague to be a column name. We laugh at people who use the meta data prefix “vw_” on table names; we call them “Volkswagen programmers”. There is no such thing as a magical, universal “id”; it has to be something in particular; remember the laws of logic? Your magical “id” is a location or a date or a squid or an automobile or ..


    A calendar has a natural key, the calendar date, yet you invent a magical “id” for it! There is an ISO country code (not id)that is three letters.


    We avoid math in the DB and just pass the raw material to the front end. And we wodul never it the way you were trying to do it.


    Here is a very quick attempt to clean up your code and trim out needless code. Did you mean for '2011-11-15' to appear in both counts?


    SELECT OC.occ_family_id, OC.occ_family_name,

    SUM (CASE WHEN C.cal_date BETWEEN '2011-11-01'

    AND '2011-11-15'

    THEN J.job_opening_cnt ELSE 0 END)

    AS cd_cnt,

    SUM (CASE WHEN C.cal_date BETWEEN '2011-11-15'

    AND '2011-11-30'

    THEN J.job_opening_cnt ELSE 0 END)

    AS pd_cnt

    FROM Jobs AS J,

    Job_Location AS JL,

    Calendar AS C,

    OnetCodes AS OC

    WHERE JL.location_id = J.location_id

    AND C.cal_date = J.job_date

    AND OC.onet_code = J.onet_code

    AND JL.country_code = 'USA'

    AND JL.state_code = 'CA'

    AND OC.something_stuff NOT IN (1, 1392)

    GROUP BY OC.occ_family_id, OC.occ_family_name;


    The next trick is a report period calendar. It gives a name to a range of dates.


    CREATE TABLE Report_Periods

    (report_name VARCHAR(30) NOT NULL PRIMARY KEY,

    report_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,

    report_end_date DATE NOT NULL,

    CONSTRAINT date_ordering

    CHECK (report_start_date <= report_end_date),

    etc);


    These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. There can be gaps between them; we have to wait a year between each “Annual Going out Of Business Sale!” and there might be long stretches of time without any special sales. But we want to know their ranges so that the table is fairly constant once it is created.


    I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL.





    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, March 2, 2012 8:37 PM
  • Hi all,

    As an additional information,

    i am given with this MDX query (this is a part of the query where the actual calculations are done)

    WITH 

    SET [PDCount] AS '{  [Calendar].[Hierarchy].[Day].&[2011]&[11]&[1]:[Calendar].[Hierarchy].[Day].&[2011]&[11]&[15]  }' 
    SET [CDCount] AS '{  [Calendar].[Hierarchy].[Day].&[2011]&[11]&[15]:[Calendar].[Hierarchy].[Day].&[2011]&[11]&[30] }'
     MEMBER Measures.[Val1] AS 'Aggregate([CDCount],[Measures].[Canon Number Of Openings])'
     MEMBER Measures.[Val2] AS 'Aggregate([PDCount],[Measures].[Canon Number Of Openings])'
     MEMBER Measures.[Val3] AS '0' 
     MEMBER Measures.[Per1] AS 'Round((Measures.[Val1])*100 /sum(Root(),Measures.[Val1]),2)'  
     MEMBER Measures.[Per2] AS 'Round((Measures.[Val2])*100/sum(Root(),Measures.[Val2]),2)'
     MEMBER Measures.[Per3] AS '0' 

    And i am supposed to write the equivalent SQl query for this, (this is also a part of the query where the actual calculations are done)

    SELECT TOP 200 OC.OccFamilyCode, OC.OccFamilyName
    ,SUM (case when c.date between '2011-11-01' and '2011-11-15' then J.Canonnumberofopenings else 0 end) as CDCOUNT
    ,SUM (case when c.date between '2011-11-15' and '2011-11-30' then J.Canonnumberofopenings else 0 end) as PDCOUNT
    ,Convert(decimal(5,2),(SUM (case when c.date between '2011-11-01' and '2011-11-15' then J.Canonnumberofopenings else 0 end)*100.0
    /(sum(SUM (case when c.date between '2011-11-01' and '2011-11-15' then J.Canonnumberofopenings else 0 end))over()))) as Per1
    ,Convert(decimal(5,2),(SUM (case when c.date between '2011-11-15' and '2011-11-30' then J.Canonnumberofopenings else 0 end)*100.0
    /(sum(SUM (case when c.date between '2011-11-15' and '2011-11-30' then J.Canonnumberofopenings else 0 end))over()))) as Per2

    Now i am able to get the Percentage column for my SQL but still the results from MDX query is not exactly  matching with my SQl result only for the Per1 and Per2 columns but the other all are matching.

    My understanding it that the root() in MDX will be replaced by over() in SQL but i dont know why the results are still not matching.

    Can you please help me with this?

    Thanks,

    Aish

    Saturday, March 3, 2012 5:38 PM
  • Hi all,

    I myself explored and got this done in other way like this,

    Convert(decimal(5,2),SUM (case when c.date between '2010-11-1' and '2010-11-30' then J.Canonnumberofopenings else 0 end)*100.0 /(Select Sum(J1.CanonNumberOfOpenings) from dw_Job J1 inner join Calendar C1 on C1.ID=J1.JobDateID where c1.date between '2010-11-1' and '2010-11-30')) as Per1

    ,Convert(decimal(5,2),SUM (case when c.date between '2011-11-1' and '2011-11-30' then J.Canonnumberofopenings else 0 end)*100.0  /(Select Sum(J1.CanonNumberOfOpenings) from dw_Job J1 inner join Calendar C1 on C1.ID=J1.JobDateID where c1.date between '2011-11-1' and '2011-11-30')) as Per2 

    Thanks,

    Aish


    • Marked as answer by IshNair Monday, March 5, 2012 9:33 AM
    Monday, March 5, 2012 9:33 AM