locked
How to Recursively Sum a Sum? RRS feed

  • Question

  • Sorry, everyone, this is driving me nuts.  I have the following query, which gives me the relative percentage of [INDEX MARKET CAP].

    SELECT *, [INDEX MARKET CAP] * 100/SUM([INDEX MARKET CAP]) OVER() as [%]
    FROM [S&P_Emerging_SmallCap_(US_Dollar)]

    So, I get a sum of the [INDEX MARKET CAP], and then find the relative percentage of each items in the INDEX MARKET CAP.  Great!  now, I'm trying to modify the query a bit to give me the SUM of each relative INDEX MARKET CAP.  So, the sum will be 100%!  I just want to prove that the query works.

    I'm trying to follow the example here.

    http://technet.microsoft.com/en-us/library/ms190766%28v=sql.105%29.aspx

    It seems so simple, but this dang thing just won't work.

    I think it should be something like this.

    ;With CTE (Company, [%])
    AS
    SELECT *, [INDEX MARKET CAP] * 100/SUM([INDEX MARKET CAP]) OVER() as [%]
    FROM [S&P_Emerging_SmallCap_(US_Dollar)] 
    Select Company, SUM([%]) as Total
    FROM CTE
    Group By Company, Total

    I keep getting this.

    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'SELECT'.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, July 10, 2014 6:48 PM

Answers

  • Your percents are for the whole set, but the second query is grouping by the Company. So, you will not get 100%. If you remove Company and just get the sum, you should get 100%.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by ryguy72 Wednesday, October 8, 2014 1:18 AM
    Thursday, July 10, 2014 8:53 PM
  • You have bad syntax. Try this:

    ;With CTE as (
    SELECT company, [INDEX MARKET CAP] * 100/SUM([INDEX MARKET CAP]) OVER() as [%]
    FROM [S&P_Emerging_SmallCap_(US_Dollar)] )
    
    Select Company, SUM([%]) as Total
    FROM CTE
    Group By Company, Total

    • Marked as answer by ryguy72 Wednesday, October 8, 2014 1:19 AM
    Thursday, July 10, 2014 6:52 PM
  • This is how you can remove your error:

    ;With CTE (Company, [%])
    AS
    (
    SELECT *, [INDEX MARKET CAP] * 100/SUM([INDEX MARKET CAP]) OVER() as [%]
    FROM [S&P_Emerging_SmallCap_(US_Dollar)] 
    )
    Select Company, SUM([%]) as Total
    FROM CTE
    Group By Company, Total


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    • Marked as answer by ryguy72 Wednesday, October 8, 2014 1:19 AM
    Thursday, July 10, 2014 6:53 PM
  • Try this:

    ;With CTE
    AS
    (
    SELECT *, [INDEX MARKET CAP] * 100/SUM([INDEX MARKET CAP]) OVER() as [%]
    FROM [S&P_Emerging_SmallCap_(US_Dollar)] 
    )
    Select Company, SUM([%]) as Total
    FROM CTE
    Group By Company


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    • Marked as answer by ryguy72 Wednesday, October 8, 2014 1:19 AM
    Thursday, July 10, 2014 7:06 PM
  • DECLARE @nasdaq table (tikr char(5), pct float)
    INSERT INTO @nasdaq (tikr, pct)
    VALUES  
    ('GOOG', .25),('GOOGL', .25),('MSFT', .125),('AAPL', .125),('AMZN', .125),('FTSE', .125)
    
    SELECT *
      FROM @nasdaq
        INNER JOIN (SELECT SUM(pct) AS [%] FROM @nasdaq) a
    	ON tikr = tikr

    • Marked as answer by ryguy72 Wednesday, October 8, 2014 1:18 AM
    Thursday, July 10, 2014 7:57 PM
    • Marked as answer by ryguy72 Wednesday, October 8, 2014 1:18 AM
    Wednesday, July 23, 2014 9:08 PM
  • DECLARE @index TABLE (ticker VARCHAR(5), marketCap FLOAT, price FLOAT)
    INSERT INTO @index (ticker, marketCap, price)
    VALUES  
    ('GOOG', 403.33, 595.98),
    ('GOOGL', 409.05, 605.19),
    ('MSFT', 369.68, 44.87),
    ('AAPL', 582.09, 97.19),
    ('INTC', 171.53, 34.50),
    ('IBM', 195.99, 193.63),
    ('NFLX', 25.71, 427.9),
    ('VMW', 40.73, 94.52),
    ('LNKD', 20.92, 171.78),
    ('FB', 182.95, 71.29)
    
    
    
    SELECT *, i.marketcap/i2.mcTotal AS pct
      FROM @index i
        INNER JOIN (
    	            SELECT SUM(marketCap) AS mcTotal FROM @index 
    			   ) i2
    	  ON i2.mcTotal = i2.mcTotal
        INNER JOIN (SELECT SUM(marketcap/mcTotal) AS sPct
                      FROM @index i
                        INNER JOIN (
    	                            SELECT SUM(marketCap) AS mcTotal FROM @index 
    			                   ) i2
    	                  ON i2.mcTotal = i2.mcTotal
                    ) s
          ON s.sPct = s.sPct

    Is this, in essence what you're trying to achieve?
    • Marked as answer by ryguy72 Wednesday, October 8, 2014 1:18 AM
    Wednesday, July 23, 2014 10:18 PM

All replies

  • You have bad syntax. Try this:

    ;With CTE as (
    SELECT company, [INDEX MARKET CAP] * 100/SUM([INDEX MARKET CAP]) OVER() as [%]
    FROM [S&P_Emerging_SmallCap_(US_Dollar)] )
    
    Select Company, SUM([%]) as Total
    FROM CTE
    Group By Company, Total

    • Marked as answer by ryguy72 Wednesday, October 8, 2014 1:19 AM
    Thursday, July 10, 2014 6:52 PM
  • This is how you can remove your error:

    ;With CTE (Company, [%])
    AS
    (
    SELECT *, [INDEX MARKET CAP] * 100/SUM([INDEX MARKET CAP]) OVER() as [%]
    FROM [S&P_Emerging_SmallCap_(US_Dollar)] 
    )
    Select Company, SUM([%]) as Total
    FROM CTE
    Group By Company, Total


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    • Marked as answer by ryguy72 Wednesday, October 8, 2014 1:19 AM
    Thursday, July 10, 2014 6:53 PM
  • Patrick, I get this:

    Invalid column name 'TOTAL'.

    Itz, now I see that I was missing the brackets; that was silly.  That script does look good, but I get this:

    'CTE' has more columns than were specified in the column list.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, July 10, 2014 7:03 PM
  • Try this:

    ;With CTE
    AS
    (
    SELECT *, [INDEX MARKET CAP] * 100/SUM([INDEX MARKET CAP]) OVER() as [%]
    FROM [S&P_Emerging_SmallCap_(US_Dollar)] 
    )
    Select Company, SUM([%]) as Total
    FROM CTE
    Group By Company


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    • Marked as answer by ryguy72 Wednesday, October 8, 2014 1:19 AM
    Thursday, July 10, 2014 7:06 PM
  • That runs, and I actually had this before, but it doesn't sum the relative percentages.  I would think that it would be like this, but I'd have one final column at the end with all 1s.

    It seems like the Sum isn't working.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, July 10, 2014 7:28 PM
  • DECLARE @nasdaq table (tikr char(5), pct float)
    INSERT INTO @nasdaq (tikr, pct)
    VALUES  
    ('GOOG', .25),('GOOGL', .25),('MSFT', .125),('AAPL', .125),('AMZN', .125),('FTSE', .125)
    
    SELECT *
      FROM @nasdaq
        INNER JOIN (SELECT SUM(pct) AS [%] FROM @nasdaq) a
    	ON tikr = tikr

    • Marked as answer by ryguy72 Wednesday, October 8, 2014 1:18 AM
    Thursday, July 10, 2014 7:57 PM

  • Thanks, but that creates a Cartesian Product.

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, July 10, 2014 8:36 PM
  • Your percents are for the whole set, but the second query is grouping by the Company. So, you will not get 100%. If you remove Company and just get the sum, you should get 100%.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by ryguy72 Wednesday, October 8, 2014 1:18 AM
    Thursday, July 10, 2014 8:53 PM
  • This may be part of what you're trying to achieve?  Start with just the Rollup to see if it's close to what you want, then also take a look at Grouping_ID as well to make it more readable/usable too

    Group by Rollup(Company, Total)

    • Proposed as answer by Fanny Liu Friday, July 11, 2014 8:50 AM
    • Unproposed as answer by Fanny Liu Friday, July 11, 2014 8:50 AM
    • Proposed as answer by Avijit Swain Thursday, July 24, 2014 7:54 AM
    Thursday, July 10, 2014 10:57 PM
    • Marked as answer by ryguy72 Wednesday, October 8, 2014 1:18 AM
    Wednesday, July 23, 2014 9:08 PM
  • DECLARE @index TABLE (ticker VARCHAR(5), marketCap FLOAT, price FLOAT)
    INSERT INTO @index (ticker, marketCap, price)
    VALUES  
    ('GOOG', 403.33, 595.98),
    ('GOOGL', 409.05, 605.19),
    ('MSFT', 369.68, 44.87),
    ('AAPL', 582.09, 97.19),
    ('INTC', 171.53, 34.50),
    ('IBM', 195.99, 193.63),
    ('NFLX', 25.71, 427.9),
    ('VMW', 40.73, 94.52),
    ('LNKD', 20.92, 171.78),
    ('FB', 182.95, 71.29)
    
    
    
    SELECT *, i.marketcap/i2.mcTotal AS pct
      FROM @index i
        INNER JOIN (
    	            SELECT SUM(marketCap) AS mcTotal FROM @index 
    			   ) i2
    	  ON i2.mcTotal = i2.mcTotal
        INNER JOIN (SELECT SUM(marketcap/mcTotal) AS sPct
                      FROM @index i
                        INNER JOIN (
    	                            SELECT SUM(marketCap) AS mcTotal FROM @index 
    			                   ) i2
    	                  ON i2.mcTotal = i2.mcTotal
                    ) s
          ON s.sPct = s.sPct

    Is this, in essence what you're trying to achieve?
    • Marked as answer by ryguy72 Wednesday, October 8, 2014 1:18 AM
    Wednesday, July 23, 2014 10:18 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. Learn how to follow ISO-11179 data element naming conventions and formatting rules. The garbage you posted has spaces in it! That means that you cannot use those names in any ISO standard programming. You are an 1950's COBOL Programmer who does not understand tiered architectures, so he writes headers for display instead of having a presentation layer. In fact, you are even worse with all that meta-data garbage in your data elements. USD is a unit of measurement on a scale, not part of a data element name. Is there any way you can get a book or course in basic data modeling?? 

    Why do you think that “company” is a clear, precise data element name that is an industry standard? It is not! This generic, vague and useless. I would guess that you are using a ticker symbol or a DUNS as the identifier. But it might be “company_<anything>” or worse. What is that generic “total”? Why did you destroy any hope of a data dictionary with crap like [%]?? First of all, we use double quotes in ANSI/ISO Standard SQL, so this disaster should have been “%” without the brackets. Why do you think you can use that  one character name anywhere else? What language? The best it could be is "generic_percentage", so that programmers that follow you will know it is crap. Pros write code for the maintenance guys that have not been hired yet, not for personal spreadsheet use. 

    50 years ago when you were writing COBOL, there was a hierarchical record structure. In RDBMS, rows are not records and columns are not fields. The fields (attributes) are drawn from domains of scalar values. The names exist in those domains. Your automobile is a VIN everywhere in the universe! It is not local. 

    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 SQL forums. 

    >> Sorry, everyone, this is driving me nuts. I have the following query, which gives me the relative percentage of [INDEX MARKET CAP].
    SELECT *, [INDEX MARKET CAP] * 100/SUM([INDEX MARKET CAP]) OVER() AS "generic_percentage" FROM [S&P_Emerging_SmallCap_(US_Dollar)] <<

    >> So, I get a sum of the [INDEX MARKET CAP], and then find the relative percentage of each items in the INDEX MARKET CAP.  Great!  now, I'm trying to modify the query a bit to give me the SUM of each relative INDEX MARKET CAP. So, the sum will be 100%! <<

    No, it will not. In fact, you will be lucky to get 99.99% -- do you understand rounding errors? Since you were rude (really, really rude by SQL Forum standard, in fact)  We have no idea what data types we have here; we have to guess you used DECIMAL(s,p) so you might get better answers with “index_market_cap * 100.0000 /SUM(index_ market_cap) OVER() AS index_market_cap_percentage”; See the decimal places? Unlike spreadsheet, we have to worry about this in SQL. 

    As far as your error goes, look at the CTE column list and the GROUP BY; they do not match ! 

    --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

    Thursday, July 24, 2014 12:06 AM
  • Good day ryguy72

    Is this still an issue?

    This is minimal polite behavior responding people that tried to help you, and close the thread in the end :-)

    Thanks!


    [Personal Site]  [Blog]  [Facebook]
    signature

    Tuesday, October 7, 2014 10:36 PM
  • All set with everything.  Thanks to all.

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, October 8, 2014 1:18 AM