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,
ASKED: April 17, 2008  10:58 PM
UPDATED: April 18, 2008  5:04 PM

Answer Wiki

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.

This makes your query
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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following