Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have run into some pre-existing SQL that I'm having a hard time uderstanding.

SELECT
    MAX(I.Symbol) Symbol
,   MAX(I.Ticker) CUSIP
,   MAX(I.Name) Name
,   SUM(H.Quantity) TotalQuantity
,   SUM(H.MarketValue) TotalMarketValue
,   MAX(H.PriceLC) Price
,   MAX(I.CategoryCode5) BUY_SELL
,   MAX(I.EquivFactor1) PriceTgt
,   MAX(P.LastPrice) CurrPrice
,   MAX(I.AssetClass) Target
,   MAX(I.Industry) Industry
,   MAX(I.CategoryCode1) Risk
FROM
    HOLDINGS_SECURE H
,   INVESTMENTS I
,   PRICE P
WHERE
    H.Symbol = I.Symbol
    AND I.Product = 'stock'
    AND H.Quantity > 0
    AND I.CategoryCode5 NOT IN ('X', '')
    AND H.Symbol = P.Symbol
GROUP BY
    I.Symbol

Symbol, Ticker, Name, CategoryCode5, Assest Class, Industry, and CategoryCode1 are all varchar fields. The remaining fields are decimals.

My best educated guess is that somehow max is being used to avoid multiple grouping columns but how can this return the correct results?

Tables Relational Diagram

share|improve this question
2  
My first guess is that they used the aggregation because they've missed a relationship between the INVESTMENTS table and the other two tables. –  billinkc Jan 7 '13 at 17:13
1  
Any time I've ever seen this, or a query with a GROUP BY clause that includes almost every column, it's either been because of a missing join condition, or the query author hacking around the fact there are duplicate rows in the data. –  Jon Seigel Jan 7 '13 at 17:29
1  
>>how can this return the correct results? -maybe it doesn't. –  JM Hicks Jan 7 '13 at 17:39
1  
Please edit your question and provide the tables' structure. Or at least the Primary keys and Unique constraints that you have. –  ypercube Jan 7 '13 at 18:31

2 Answers 2

up vote 8 down vote accepted

You say: "My best educated guess is that somehow max is being used to avoid multiple grouping columns"

That is correct.

and then: "... but how can this return the correct results?"

It returns correct results because the Symbol is the primary key in both the Investments and the Price tables. Therefore, any aggregate function over a P.column or an I.column is aggregating identical values. And MAX(c) when c is 2, 2, 2 or 2 is of course 2.

Could the query be written somehow else, possibly without all these aggregations? Yes, see a related question: Why do wildcards in GROUP BY statements not work?

It would have to be a rather long GROUP BY clause or have the aggregations moved into a subquery with only the Holdings_Secure table (where Symbol is not the Primary key) and then joined to the other two:

SELECT
    I.Symbol Symbol
,   I.Ticker CUSIP
,   I.Name Name

,   H.TotalQuantity
,   H.TotalMarketValue
,   H.Price

,   I.CategoryCode5 BUY_SELL
,   I.EquivFactor1 PriceTgt
,   P.LastPrice CurrPrice
,   I.AssetClass Target
,   I.Industry Industry
,   I.CategoryCode1 Risk
FROM
    ( SELECT 
          SUM(Quantity) TotalQuantity
      ,   SUM(MarketValue) TotalMarketValue
      ,   MAX(PriceLC) Price
      ,   Symbol
      FROM
          HOLDINGS_SECURE
      WHERE
          Quantity > 0
      GROUP BY
          Symbol
    ) H
  JOIN
    INVESTMENTS I
      ON H.Symbol = I.Symbol
  JOIN
    PRICE P
      ON H.Symbol = P.Symbol
WHERE
        I.Product = 'stock'
    AND I.CategoryCode5 NOT IN ('X', '') ;
share|improve this answer
    
Symbol is the primary key in both the Investments and the Price tables. Therefore, any aggregate function over a P.column or an I.column is aggregating identical values. Exactly what I was looking for. Thanks. –  W.Jackson Jan 8 '13 at 14:39

It could be that the aggregations fit better in a subquery, and make more sense when it's that subquery that's later joined to the other table INVESTMENTS and PRICE-see below.

In that case you could say that the "reason" for it could be:

  • developer inexperience w/ SQL
  • developer experience gained on SQL platform that doesn't support table subqueries
  • developer style

It could be that some data in the tables is not normalized properly, or something similar as suggested in other comments, or it could be just plain wrong.

Here's a guess as to how perhaps it could be re-written more neatly with a subquery, followed by a another query that might help analyze the data for anomalies if that turns out to be necessary.

possible rewrite:

SELECT
    I.Symbol Symbol
,   I.Ticker CUSIP
,   I.Name Name
,   H.Quantity TotalQuantity
,   H.MarketValue TotalMarketValue
,   H.PriceLC Price
,   I.CategoryCode5 BUY_SELL
,   I.EquivFactor1 PriceTgt
,   P.LastPrice CurrPrice
,   I.AssetClass Target
,   I.Industry Industry
,   I.CategoryCode1 Risk
FROM (
    --begin holdings_secure_aggregated
    SELECT
    Symbol,
    SUM(Quantity) TotalQuantity,
    SUM(MarketValue) TotalMarketValue,
    MAX(PriceLC) PriceLC
    FROM HOLDINGS_SECURE
    WHERE Quantity > 0
    GROUP BY Symbol
) H, --end holdings_secure_aggregated
INVESTMENTS I, PRICE P
WHERE
    H.Symbol = I.Symbol
    AND I.Product = 'stock'
    AND I.CategoryCode5 NOT IN ('X', '')
    AND H.Symbol = P.Symbol
GROUP BY I.Symbol;

You can use something ugly like this to see which I.Symbol values-if any-return more than one distinct value among the columns that are aggregated using MAX, and to see precisely what those individual values are.

SELECT
    I.Symbol Symbol
,   I.Ticker CUSIP
,   I.Name Name
,   H.Quantity TotalQuantity
,   H.MarketValue TotalMarketValue
,   H.PriceLC Price
,   I.CategoryCode5 BUY_SELL
,   I.EquivFactor1 PriceTgt
,   P.LastPrice CurrPrice
,   I.AssetClass Target
,   I.Industry Industry
,   I.CategoryCode1 Risk
FROM
    HOLDINGS_SECURE H
,   INVESTMENTS I
,   PRICE P
WHERE
    H.Symbol = I.Symbol
    AND I.Product = 'stock'
    AND H.Quantity > 0
    AND I.CategoryCode5 NOT IN ('X', '')
    AND H.Symbol = P.Symbol
    AND EXISTS (
        SELECT I.Symbol
        FROM
            HOLDINGS_SECURE H
        ,   INVESTMENTS I
        ,   PRICE P
        WHERE
            H.Symbol = I.Symbol
            AND I.Product = 'stock'
            AND H.Quantity > 0
            AND I.CategoryCode5 NOT IN ('X', '')
            AND H.Symbol = P.Symbol
        GROUP BY
            I.Symbol
        HAVING (
            COUNT(DISTINCT I.Ticker)
            + COUNT(DISTINCT I.Name)
            + COUNT(DISTINCT H.PriceLC)
            + COUNT(DISTINCT I.CategoryCode5)
            + COUNT(DISTINCT I.EquivFactor1)
            + COUNT(DISTINCT P.LastPrice)
            + COUNT(DISTINCT I.AssetClass)
            + COUNT(DISTINCT I.Industry)
            + COUNT(DISTINCT I.CategoryCode1)
        )
        > 0
    );
share|improve this answer
    
You answer was helpful but I marked ypercube's answer as accepted because he/she explained why the original query worked. Thank you for your answer. –  W.Jackson Jan 8 '13 at 13:48
    
You're welcome, and I also think ypercube's was the best answer. –  JM Hicks Jan 8 '13 at 14:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.