## Count several columns Group by accounts Greater than value Beginner

5 pts.
Tags:
COUNT
GROUP BY statement
SQL
I need to be able to count how many values are greater than 1 for each StuNum. I have only been able to count how many values are greater than 1 for each StuNum for d1 or for d2 or for d3, but not for d1, d2 and d3. How can I have it count multiple columns. Table StuNum – SchNum – Tchnum – d1 – d2 – d3 – d4 – d5 – d6 999999 – 101 - 4545 - 1 - 0 – 2 – 0 – 1 – 3 999999 – 202 – 3636 – 0 - 1 – 0 – 2 – 1 – 0 444444 - 202 – 3636 – 0 – 1 - 0 – 0 – 0 – 1 555555 - 303 - 7878 – 0 - 2 – 3 – 0 – 0 – 1 SELECT COUNT(d1) AS Doubles, StuNum, SchNum, TchNum FROM table WHERE (d1 > 1) GROUP BY StuNum, SchoolNum, TeachNum Thanks,
Related Questions

Thanks. We'll let you know when a new response is added.

I would try this as a union all statement

SELECT ‘d1’,COUNT(d1) AS Doubles, StuNum, SchNum, TchNum
FROM table
WHERE (d1 > 1)
GROUP BY StuNum, SchoolNum, TeachNum
UNION ALL
SELECT ‘d2’,COUNT(d2) AS Doubles, StuNum, SchNum, TchNum
FROM table
WHERE (d2 > 1)
GROUP BY StuNum, SchoolNum, TeachNum
UNION ALL
SELECT ‘d3’,COUNT(d3) AS Doubles, StuNum, SchNum, TchNum
FROM table
WHERE (d3 > 1)
GROUP BY StuNum, SchoolNum, TeachNum;

My other thought is to re-organize the table. Normalization tells us you don’t store the same data in multiple columns, you use multiple rows. I would create your table as follows:

Table
StuNum,
SchNum,
TchNum,
<b>D_Type</b>,
d_count

D_type would be ‘d1′,’d2′,’d3’, etc.

SELECT StuNum, SchNum, TchNum, d_type, COUNT(d1) AS Doubles
FROM table
WHERE (d_count > 1)
GROUP BY D_type,StuNum, SchoolNum, TeachNum;

List would list all the various types where their count is > 1.