I am trying to get an SQL Server Pivot table to work that allows me to count and then sum a number of columns (6 in total). The purpose of the pivot table is to aggregate online questionnaire results for any number of production sites. There are 6 questions which can have 3 result values - Target, Action and Fail. What I am trying to do is count up the number of Target, Action and Fail for each question, and to then sum this up for each. So, for example, Production Site A could have 2 Target, 2 Action and 2 Fail.
My understanding is that the SQL Server Pivot table could deliver this information, which can then be display in ASP.Net ReportViewer. Below is my code, but it is not working, and could do with some expert help:
SELECT PRODUCTION_Site,
[Target],
[Action],
[Fail]
FROM
(SELECT Production_Site,
SUM(Coding),
SUM(Measurable),
SUM(Appearance),
SUM(Aroma),
SUM(Flavour),
SUM(Texture)
FROM t_Pqe_Grocery
GROUP BY Production_Site) AS T
PIVOT
(
COUNT(Coding) FOR Grocery_Packaging_And_Coding IN ([Target],[Action],[Fail])
COUNT(Measurable) FOR Grocery_Measurable IN ([Target],[Action],[Fail])
COUNT(Appearance) FOR Grocery_Appearance IN ([Target],[Action],[Fail])
COUNT(Aroma) FOR Grocery_Aroma IN ([Target],[Action],[Fail])
COUNT(Flavour) FOR Grocery_Flavour IN ([Target],[Action],[Fail])
COUNT(Texture) FOR Grocery_Texture IN ([Target],[Action],[Fail])) AS P
Is there a way round this, or is Pivot table not the solution?
Table is
Production_Site,
Grocery_Packaging_And_Coding,
Grocery_Measurable,
Grocery_Appearance,
Grocery_Aroma,
Grocery_Flavour,
Grocery_Texture
Data in the table would like this:
Site A, Target, Action, Fail, Target, Target, Target
Site B, Target, Action, Fail, Target, Target, Target
Site C, Target, Target, Target, Target, Target, Target
Site A, Target, Target, Target, Target, Target, Target
The result I am looking for is
Production_Site | Target | Action | Fail
Site A 10 1 1
Site B 4 1 1
Site C 6 0 0
SQL SUM() and COUNT() using variable SUM of values of a field or column of a SQL table, generated using SQL SUM() function can be stored in a variable or temporary column referred as alias. The same approach can be used with SQL COUNT() function too.
If you need to add a group of numbers in your table you can use the SUM function in SQL. This is the basic syntax: SELECT SUM(column_name) FROM table_name; If you need to arrange the data into groups, then you can use the GROUP BY clause.
Of course it is not possible to combine two different values resulting from two aggregate functions only in a single column.
A much easier way to perform this query would be to apply both the UNPIVOT
and then the PIVOT
functions:
select *
from
(
select Production_Site, value
from t_Pqe_Grocery
unpivot
(
value
for col in (Grocery_Packaging_And_Coding, Grocery_Measurable,
Grocery_Appearance, Grocery_Aroma,
Grocery_Flavour, Grocery_Texture)
) unp
) src
pivot
(
count(value)
for value in ([Target], [Action], [Fail])
) piv
See SQL Fiddle with Demo
The UNPIVOT
takes your column list and transform it into multiple rows which makes it much easier to count:
select Production_Site, value
from t_Pqe_Grocery
unpivot
(
value
for col in (Grocery_Packaging_And_Coding, Grocery_Measurable,
Grocery_Appearance, Grocery_Aroma,
Grocery_Flavour, Grocery_Texture)
) unp
Unpivot Result:
| PRODUCTION_SITE | VALUE |
----------------------------
| Site A | Target |
| Site A | Action |
| Site A | Fail |
| Site A | Target |
| Site A | Target |
| Site A | Target |
| Site B | Target |
| Site B | Action |
| Site B | Fail |
| Site B | Target |
| Site B | Target |
| Site B | Target |
| Site C | Target |
| Site C | Target |
| Site C | Target |
| Site C | Target |
| Site C | Target |
| Site C | Target |
| Site A | Target |
| Site A | Target |
| Site A | Target |
| Site A | Target |
| Site A | Target |
| Site A | Target |
Then applying the PIVOT
to this will get the count that you want for each of the PRODUCTION_SITE
s. After adding the PIVOT
the result is:
| PRODUCTION_SITE | TARGET | ACTION | FAIL |
--------------------------------------------
| Site A | 10 | 1 | 1 |
| Site B | 4 | 1 | 1 |
| Site C | 6 | 0 | 0 |
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With