• Thanks Hugo for the following. I will have to dive into grouping sets a bit further.

    Hugo Kornelis (5/31/2013)


    I don't get it.

    First, the idea of the question. We are given SQL to create and populate a table, *and* SQL that (supposedly?) shows the correct answers. Are we supposed to run this? Work it out in our heads? On a SQL Forum, I'd much sooner expect the task to be to WRITE the query to find the correct answers!

    Second, the explanation. It appears to be incomplete. It doesn't start with how I expect a sentence to start, and at the end is an empty dropdown. It appears to be part of the explanation of GROUPING SETS, but there is no reference made at all to the question or answers.

    Third, the query given. Even though the explanation mentions the requirement of aggregate functions in the SELECT, the query uses two columns not included in the GROUP BY and not in an aggregate. That causes a lot of NULL values in the result set, and (which is more important) misleading information. Also, the GROUPING SETS specification is only partially related to the questions asked, and the GROUPING_ID has the wrong column specification, causing the grp_id column to be useless.

    Fourth, the answers supposed to be correct. They are not. At least not all.

    1. "The highest value sold to (one) custid" is $ 723.79 (to customer B), as can be seen from running this query:

    SELECT custid, SUM(TotalSales) AS TotalValue

    FROM dbo.Orders

    GROUP BY custid

    ORDER BY TotalValue DESC;

    2. "The highest quantity sold to any / a single custid" depends on whether this refers to the total of all sales to a customer, or to the highest in a single sale. In the first case, it is 72. In the second case, it is 40.

    SELECT custid, SUM(qty) AS Sumqty, MAX(qty) AS Maxqty

    FROM dbo.Orders

    GROUP BY custid;

    3. "Empid with highest value sold to a single custid is 4" / "Empid with highest value sold is 3" - I thought these were both true when going through this in my head, but it turns out I was wrong. Employee 4 sells a total of $ 418.10 to customer A and nothing else. Employee sells a total of $612.45, but spread over different customers. However, I had overlooked that one of those customers is good for $499.95, so employee 3 also sold the highest to a single custid.

    SELECT empid, custid, SUM(TotalSales) AS TotalValue

    FROM dbo.Orders

    GROUP BY ROLLUP(empid, custid)

    ORDER BY TotalValue DESC;

    4. 2013 is the year with the highest quantity sold:

    SELECT YEAR(orderdate) AS SaleYear, SUM(TotalSales) AS TotalValue

    FROM dbo.Orders

    GROUP BY YEAR(orderdate);

    So, not four correct answers (as Ron thought when submitting the question), not two (as I thought when answering), but three.

    Oh, and for those who want to learn about GROUPING SETS and GROUPING_ID, here are two references:

    http://msdn.microsoft.com/en-us/library/ms177673.aspx

    http://msdn.microsoft.com/en-us/library/bb510624.aspx

    And here's a single query that uses GROUPING_SETS to find all the relevant answers. I've included a corrected GROUPING_ID as well.

    SELECT GROUPING_ID(custid,

    empid,

    YEAR(orderdate)) AS grp_id,

    empid, custid, YEAR(orderdate) AS SaleYear,

    SUM(qty) AS TotalQty, SUM(TotalSales) AS SalesValue,

    SUM(qty) AS Sumqty, MAX(qty) AS Maxqty

    FROM dbo.Orders

    GROUP BY GROUPING SETS (custid, -- Total per customer

    empid, -- Total per employee

    (custid, empid), -- Per employee / customer combination

    YEAR(orderdate)) -- Per year

    ORDER BY grp_id;

    Note that the grp_id column is a bitmap that holds a 1 in the corresponding bit for the aggregated columns, and the LAST column is 1. So the year is 1, empid 2, and custid is 4.

    The value 6 is 4 + 2 (or binary 110), meeaning that empid and custid are aggregated, but year is not - so these rows represent the total per year.

    For the totals per employee/customer combination, I want year to be aggregated and empid and customer not - so that would be 1 (binary 001).

    For the per-customer totals, I want employee and year aggregated, so I need 2 + 1 = 3 (binary 011).

    And the value 5 finally represents the per-employee totals (binary 101, or 4+1, so customer and year aggregated).

    Bottom line: using GROUPING SETS is hard. Very hard. It is a useful and very powerful instrument, but it takes a lot of time to learn to wield properly. (I think I have spent at least an hour reading up and trying things before I understood enough to be able to write this reply). Anyone who thinks they can benefit from this often-overlooked function should take the time to get to know this instrument.

    Thanks, Ron, for the question. I do not think it is a good question, but it did prompt me to read up on GROUPING SETS and GROUPING_ID, and expand my knowledge - so it is useful after all! 😉

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events