Grouped Queries

Question 1

What is a grouped query

Answer 1

A query that contains
1. An aggregate function
2. A group by clause
3. Both

SQL Server 70-461 05-01

Question 2

How many rows are returned per group?

Answer 2

1

SQL Server 70-461 05-01

Question 3

What is the difference between WHERE and HAVING

Answer 3

• WHERE is evaluated at the row level
• HAVING is evaluated at the group level

SQL Server 70-461 05-01

Question 4

Does WHERE or HAVING come first in logical query processing?

Answer 4

• WHERE is before HAVING in logical query processing

SQL Server 70-461 05-01

Question 5

What aggregate functions does T-SQL support?

Answer 5

1. Count(*)
2. Count(FieldName)
3. SUM
4. AVG
5. MIN
6. MAX

SQL Server 70-461 05-01

Question 6

How are nulls handled by aggregate functions?

Answer 6

• For all except Count(*), they are ignored

SQL Server 70-461 05-01

Question 7

What is the difference between count(*) and count(FieldName)

Answer 7

• count(*) includes nulls
• count(FieldName) DOES NOT include nulls

SQL Server 70-461 05-01

Question 8

Where is DISTINCT placed when using it in an aggregate function

Answer 8

SELECT 
shipperid,
count(DISTINCT FieldName) AS         
numshippingdates 
FROM sales.orders
GROUP BY shipperid

SQL Server 70-461 05-01

Question 9

What aggregate function is DISTINCT most commonly used with

Answer 9

count

• example: count(DISTINCT shippeddate)

SQL Server 70-461 05-01

Question 10

Why would you use a grouping set clause

Answer 10

• To write one succinct statement instead of, for example, four separate GROUP BY queries combined using UNION ALL

SQL Server 70-461 05-01

Question 11

Example grouping set clause

Answer 11

GROUP BY GROUPINGSETS
(
(shipperid, year(shippeddate)),
(shipperid                                   ),
(year(shippeddate)                   ),
(                                                   ),
);

Note: the blank parens represent an empty set

SQL Server 70-461 05-01

Question 12

What is the output of a query with multiple grouping sets

Answer 12

• The output would be the same as if you had made a single query for each grouping set and then used UNION ALL to combine them.

SQL Server 70-461 05-01

Question 13

Your three columns are shipperid, shipyear, numorders. In a GROUP BY GROUPINGSETS clause, what happens if the set doesn’t use all of the columns? For example, where shipper is is by itself in parens and shipyear is by itself in parens.

GROUP BY GROUPINGSETS
(
(shipperid, shipyear),
(shipperid                  ),
(shipyear                   ),
(                           ),
);

Answer 13

• The grouping set (shipperid ) doesn’t include shipyear so shipyear will be null in the results for this set.
• The grouping set (shipyear ) doesn’t include shipperid so shipperid will be null in the results for this set.

Note: count (*) AS numorders is always returned

SQL Server 70-461 05-01

Question 14

What are the three clauses that allow multiple groupings in one query and where are they placed?

Answer 14

1. GROUPINGSETS
2. CUBE
3. ROLLUP

Place these right after GROUP BY

SQL Server 70-461 05-01

Question 15

What is the general form of the GROUP BY CUBE expression and how is it similar to GROUP BY GROUPING SETS?

Answer 15

GROUP BY CUBE(col1, col2, col3, etc)

GROUP BY CUBE(shipperid, year(shippeddate)

-Is the same as-

GROUP BY GROUPINGSETS
(
(shipperid, year(shippeddate)),
(shipperid ),
(year(shippeddate) ),
( ),
);

SQL Server 70-461 05-01

Question 16

What is the general format of the GROUP BY ROLLUP expression?

Answer 16

GROUP BY ROLLUP (col1, col2, col3, etc)

SQL Server 70-461 05-01

Question 17

How does GROUP BY ROLLUP work? And how is it different from GROUP BY CUBE?

Answer 17

GROUP BY ROLLUP accepts a list of inputs and, unlike GROUP BY CUBE, it does not return all possible combinations of the inputs as grouping sets. It assumes a hierarchy.

GROUP BY ROLLUP(shipcountry, shipregion, shipcity)

Is the same as

(shipcountry, shipregion, shipcity),
(shipcountry, shipregion                ),
(shipcountry                                     ),
(                                                          )
)

There are 8 possible combinations of the fields. ROLLUP only gives 4. CUBE gives all 8.

SQL Server 70-461 05-01

Question 18

How does GROUP BY CUBE work?

Answer 18

It takes a list of inputs and defines all possible grouping sets that could be generated, including the empty set.

SQL Server 70-461 05-01

Question 19

When using GROUPING SETS, CUBE or ROLLUP, can you put more than one in the GROUP BY clause?

Answer 19

Yes, just separate them by commas. When you do this you get a multiplying effect. ROLLUP(x,y,z), CUBE(A,B,C) would generate 32 grouping sets. 4 sets from ROLLUP and 8 sets from CUBE for a total of 32 sets.

SQL Server 70-461 05-01

Question 20

What does a multiple grouping set do?

Answer 20

It groups the data in more than one way in one query

SQL Server 70-461 05-01

Question 21

How does HAVING work?

Answer 21

Very similar to WHERE, except it is applied per group instead of per row.

GROUP BY comes before HAVING, so you can refer to aggregate computations in HAVING

SQL Server 70-461 05-01

Question 22

What does the empty set define?

Answer 22

One group with all rows for grand aggregates.

SQL Server 70-461 05-01

Question 23

HAVING, SELECT and ORDER BY are the last three clauses in the logical query processing phases. Why is this important to know?

Answer 23

1. They are working on a grouped table since GROUP BY already happened.
2. Expressions that appear in these clauses must guarantee a single result per group.
3. If you want to refer to elements that aren’t in the GROUP BY clause you have to apply an aggregate function to them so you will get only one value per group.

SQL Server 70-461 05-01

Question 24

What are two ways you can distinguish between nulls that are a result of grouping sets and nulls that were in the original data prior to grouping?

Answer 24

1. Grouping function
2. Grouping_ID function

SQL Server 70-461 05-01

Question 25

What is the general form of the GROUPING function?

Answer 25

_Example 1_ SELECT shipcountry, GROUPING(shipcountry) AS grpcountry, shipregion, GROUPING(shipregion) AS grpregion, shipcity, GROUPING(shipcity) AS grpcity, count(*) AS numorders FROM sales.orders GROUP BY ROLLUP (shipcountry, shipregion, shipcity) _Example 2_ ORDER BY GROUPING(c.custid) ## Footnote SQL Server 70-461 05-01

Question 26

How does the GROUPING function work?

Answer 26

It takes _one_ input. It returns 0 when the input is part of the grouping set and 1 when it isn’t. ## Footnote SQL Server 70-461 05-01

Question 27

What does the GROUPING_ID function do?

Answer 27

It accepts a list of grouped columns as inputs and returns a 0 when the element is part of the grouping set and a 1 when it isn’t. For the 1’s you take 2 raised to the power of the position minus 1. The far right column position is 1, the column to the left of it is 2, etc. Sum all the calculations and you get the GROUPING_ID. ## Footnote SQL Server 70-461 05-01

Question 28

What is the general format of the GROUPING_ID function?

Answer 28

SELECT GROUPID_ID( shipcountry, shipregion, shipcity ) AS grpid, shipcountry, shipregion, shipcity, count(*) AS numorders FROM sales.orders GROUP BY ROLLUP (shipcountry, shipregion, shipcity) ## Footnote SQL Server 70-461 05-01

Question 29

Example of GROUPING _ID with 3 columns as inputs

Answer 29

GROUPING_ID( shipcountry, shipregion, shipcity ) AS grpid shipcountry shipregion shipcity grpid 0 0 0 0 0 0 1 1 0 2 1 3 4 2 1 7 - 0=it _is_ part of the grouping set - 1=shipcountry and shipregion are part of the grouping set - 3=shipcountry only is part of the grouping set - 7=represents the empty set ## Footnote SQL Server 70-461 05-01

Question 30

What does count(*) do?

Answer 30

Counts number of rows per group including nulls. ## Footnote SQL Server 70-461 05-01

Question 31

What does a single grouping set do?

Answer 31

Groups data in only one way in one query. ## Footnote SQL Server 70-461 05-01

Question 32

What happens when you invoke a group function in a query but don’t use a GROUP BY clause?

Answer 32

All rows are arranged into one group. Example SELECT count(*) AS numorders FROM sales.orders numorders - - - - - - 830 ## Footnote SQL Server 70-461 05-01

Question 33

When all inputs are null what do the various aggregate functions return? (min, max, avg, sum, count)

Answer 33

Count returns a 0. The others return null. ## Footnote SQL Server 70-461 05-01