Solved

# Count all Forward Slashes from MySQL and Count them

Posted on 2010-09-23
488 Views
Hi there,

I'm building an attendance register. Everything works fine with it. I now have to pull the records from the database and count them all to work out the overall attendance of each student.

All the rows could have any of these characters in them so i would need to fetch each row and count each character seperately:

/
C
H
I
J
L
M
O

Can any body help me? Let me know if ya need any more information

Thanks,
Craig
0
Question by:projectstudios
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 143

Expert Comment

ID: 33744309
you mean:
``````select yourfield, count(*)
from yourtable
group by yourfield
``````
0

LVL 10

Expert Comment

ID: 33744339
Solution provided by Angelll is correct
0

LVL 4

Expert Comment

ID: 33744698
You want to calculate the numer of Cs, Hs, Os and so on?!
0

LVL 8

Expert Comment

ID: 33744767
Hi, I don't agree with the above,

If you need to count each occurance of the letters you mentioned, In a specific field,
<i would need to fetch each row and count each character seperately:

Then try this.

select
Case when Your_Field= '\\' then count(Your_Field) else '' end as 'Backslash',
Case when Your_Field = 'C' then count(Your_Field) else "" end as 'C',
Case when Your_Field = 'H' then count(Your_Field) else "" end as 'H',
Case when Your_Field = 'I' then count(Your_Field) else "" end as 'I',
Case when Your_Field = 'J' then count(Your_Field) else "" end as 'J',
Case when Your_Field = 'L' then count(Your_Field) else "" end as 'L',
Case when Your_Field = 'M' then count(Your_Field) else "" end as 'M',
Case when Your_Field = 'O' then count(Your_Field) else "" end as 'O'
from Your_table
Group by Your_Field;
0

Author Comment

ID: 33752132
Hi guys, Sorry about the long reply. Had to wait till i arrived back to work this morning.

I have tried your replies but i can't seem to get them to work.

Basically my table in my DB is attatched as an image.

As you can see each student will have a new row inserted every week under the that weeks week commencing date and then the row will be updated every day till Friday.

So i now need to pull all the specific rows to each student_id and calculate all the / marks aswell as any others so i can begin the maths to show the overall attendance percentage.

Does anybody know how i can achieve this?
Thanks,
Craig

table.jpg
0

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 500 total points
ID: 33752165
that would be:
``````select student_id
, sum(
case when mon_a = '/' then 1 else 0 end
+ case when mon_p = '/' then 1 else 0 end
+ case when tue_a = '/' then 1 else 0 end
+ case when tue_p = '/' then 1 else 0 end
+ case when wed_a = '/' then 1 else 0 end
+ case when wed_p = '/' then 1 else 0 end
+ case when thu_a = '/' then 1 else 0 end
+ case when thu_p = '/' then 1 else 0 end
+ case when fri_a = '/' then 1 else 0 end
+ case when fri_p = '/' then 1 else 0 end
) total_slashes
group by student_id
``````
0

Author Comment

ID: 33752200
Thanks for a quick reply angellll

That looks great but how do i echo the result from that?
0

Author Comment

ID: 33752212
nevermind, Done it :D
One last question for you if you don't mind.

Sometimes there might be a different character in there. Such as, C, H, I, J, L, M, O.

How do i amend that code to count those too?

Thanks
0

LVL 143

Expert Comment

ID: 33752749
>How do i amend that code to count those too?
you can add the same expression, and replace the / by the relevant code.

or do you want to count all together? aka if it's not null, count it?
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

In threads here at EE, each comment has a unique Identifier (ID). It is easy to get the full path for an ID via the right-click context menu. However, we often want to post a short link within a thread rather than the full link. This article shows aâ€¦
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve tâ€¦
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, Tâ€¦
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed usâ€¦
###### Suggested Courses
Course of the Month5 days, 20 hours left to enroll