This example uses data from NORTHWIND database that comes with every SQL Server installation.
Two tables will be used in the query: EMPLOYEES and ORDERS.
Let’s say that data analyst is preparing a presentation and interested in how many orders were placed by every particular employee during each year of operation.
Simple GROUP BY query will give quick answer to that question:
SELECT E.LastName, E.FirstName,
‘Year’ = DATEPART(year,OrderDate),
’OrdersPlaces’ = COUNT(*)
FROM EMPLOYEES E,
ORDERS O
WHERE E.EmployeeID = O.EmployeeID
GROUP BY E.LastName, E.FirstName,
DATEPART(year,OrderDate)
An the result will be the following:
|
LastName |
FirstName |
Year |
OrdersPlaced |
|
Buchanan |
Steven |
1996 |
11 |
|
Buchanan |
Steven |
1997 |
18 |
|
Buchanan |
Steven |
1998 |
13 |
|
Callahan |
Laura |
1996 |
19 |
|
Callahan |
Laura |
1997 |
54 |
|
Callahan |
Laura |
1998 |
31 |
|
… |
… |
… |
… |
|
Davolio |
Nancy |
1996 |
26 |
|
Davolio |
Nancy |
1997 |
55 |
|
Davolio |
Nancy |
1998 |
42 |
This way of presenting data is absolutely correct and completely answers the questions but not convenient for analysis and definitely cannot be placed into presentation.
The best way of presenting such results would be simulating a cross tab query using CASE operator. For each record CASE will check if it falls under specified condition (e.g. year of order date is 1996) and issue 1 (true) or 0 (false). After that SUM operator will calculate the total number of orders placed grouping it by employee last and first name.
SELECT E.LastName, FirstName,
’1996′ = SUM(CASE WHEN DATEPART(year,OrderDate) = 1996 THEN 1 ELSE 0 END),
’1997′ = SUM(CASE WHEN DATEPART(year,OrderDate) = 1997 THEN 1 ELSE 0 END),
’1998′ = SUM(CASE WHEN DATEPART(year,OrderDate) = 1998 THEN 1 ELSE 0 END)
FROM EMPLOYEES E,
ORDERS O
WHERE E.EmployeeID = O.EmployeeID
GROUP BY
E.LastName,
E.FirstName
The result of the that query:
|
LastName |
FirstName |
1996 |
1997 |
1998 |
|
Fuller |
Andrew |
16 |
41 |
39 |
|
Dodsworth |
Anne |
5 |
19 |
19 |
|
Leverling |
Janet |
18 |
71 |
38 |
|
Callahan |
Laura |
19 |
54 |
31 |
|
Peacock |
Margaret |
31 |
81 |
44 |
|
Suyama |
Michael |
15 |
33 |
19 |
|
Davolio |
Nancy |
26 |
55 |
42 |
|
King |
Robert |
11 |
36 |
25 |
|
Buchanan |
Steven |
11 |
18 |
13 |
|
Fuller |
Andrew |
16 |
41 |
39 |
|
Dodsworth |
Anne |
5 |
19 |
19 |
This way is more natural and convenient. Information is not duplicated and result can be pasted directly into the presentation document.
If you have any questions or would like to advise on how to improve this method please feel free to contact me at msmirnov@swynk.com.
