Solved

Count all Forward Slashes from MySQL and Count them

Posted on 2010-09-23
9
484 Views
Last Modified: 2012-06-21
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
Comment
Question by:projectstudios
9 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33744309
you mean:
select yourfield, count(*)

  from yourtable

 group by yourfield

Open in new window

0
 
LVL 10

Expert Comment

by:Umar Topia
ID: 33744339
Solution provided by Angelll is correct
0
 
LVL 4

Expert Comment

by:max-hb
ID: 33744698
You want to calculate the numer of Cs, Hs, Os and so on?!
0
 
LVL 8

Expert Comment

by:kingjely
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:projectstudios
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 142

Accepted Solution

by:
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

Open in new window

0
 

Author Comment

by:projectstudios
ID: 33752200
Thanks for a quick reply angellll

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

Author Comment

by:projectstudios
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now