Gossamer Forum
Home : General : Databases and SQL :

Count 2 Columns in a Table

Quote Reply
Count 2 Columns in a Table
Hello:

I'm very new to MySQL and I need to count 2 columns in the same table... and display the totals.

1) How many students are registered in a class?
Column name: `register_status` varchar (10), Values= registered, pending, Null = Yes
Count (table.registered) as Enrolled
2) How many students completed a class?
Colum name: `completion_status` varchar (10), Values= Incomplete, Complete, Null = Yes
Count (table.complete) as Finished
I tried 2 counts in the table but I get the same totals on both columns.
Any help will be greatly apreciated!
Thank you.
Quote Reply
Re: [rafermo] Count 2 Columns in a Table In reply to
1)

Quote:
SELECT COUNT(*) FROM registered WHERE register_status = 'Enrolled'

2)

Quote:
SELECT COUNT(*) FROM complete WHERE completition_status = 'Finished'

Hope that helps Smile

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Count 2 Columns in a Table In reply to
Thank you Andy!
Quote Reply
Re: [rafermo] Count 2 Columns in a Table In reply to
Hello again, MySQL is more challenging than what I expected. From the following table:

Class_ID . . . . Student_ID . . . . Register_Status . . . . Completion_Status
---------------------------------------------------------------------------------
11111 . . . . . . 12345 . . . . . . . . registered . . . . . . . . . Complete
11111 . . . . . . 67890 . . . . . . . . registered . . . . . . . . Incomplete
22222 . . . . . . 12121 . . . . . . . . registered . . . . . . . . . Complete
22222 . . . . . . 23345 . . . . . . . . registered . . . . . . . . No Show
33333 . . . . . . 56789 . . . . . . . . registered . . . . . . . . N/A
44444 . . . . . . 78910 . . . . . . . . registered . . . . . . . . Incomplete

I need to get the Completion_Status count of 'Complete' and I also need to show the number (Count) of registered students that have not completed the class. I need the output to show like below:

Class_ID . . . . Student_ID . . . . Enrolled . . . . . . . . . . Finished
---------------------------------------------------------------------------------
11111 . . . . . . 12345 . . . . . . . . . . . . . . . . . . . . . . . 1
11111 . . . . . . 67890 . . . . . . . . 1 . . . . . . . . . . . . . . .
22222 . . . . . . 12121 . . . . . . . . . . . . . . . . . . . . . . . 1
22222 . . . . . . 23345 . . . . . . . . 1 . . . . . . . . . . . . . . .
33333 . . . . . . 56789 . . . . . . . . 1 . . . . . . . . . . . . . . .
44444 . . . . . . 78910 . . . . . . . . 1 . . . . . . . . . . . . . . .
---------------------------------------------------------------------------------
Total . . . . . . . . . . .. . . . . . . . . 4 . . . . . . . . . . . . . . 2

I tried different things in MySQL but I cannot do it. I can get the output right in the Finished column but I cannot do it in the Enrolled column. Thank you in advance if you can help me to solve this problem!
rafermo

Last edited by:

rafermo: Nov 11, 2004, 11:02 AM