1

i have a code and can't figure how to count the number of occurences of the values, for example what is the total of "c"'s. numbers in db are tooth numbers and x means lack of tooth, c is caries and w is filling.

 SELECT SUM(18='w'+17='w'+16='w'+15='w'+14='w'+13='w'+12='w'+11='w'+21='w'+22='w'+23='w'+24='w'+25='w'+26='w'+27='w'+28='w'+48='w'+47='w'+46='w'+45='w'+44='w'+44='w'+43='w'+42='w'+41='w'+31='w'+32='w'+33='w'+34='w'+35='w'+36='w'+37='w'+38='w') AS liczbaw FROM badania

The table is like this:

enter image description here

YoYo
  • 9,157
  • 8
  • 57
  • 74

2 Answers2

0

First to note is however that probably you want to avoid having column names as numeric ... As per "Can a number be used to name a MySQL table column?", I have to quote with a back-tick (a backward quote).

I am actually not sure if you want to do this over multiple rows, but to aggregate over multiple rows - you can do as below

SELECT SUM(CASE WHEN `18` = 'c' THEN 1 ELSE 0 END) AS cnt_18_c FROM badania

So, I am using a CASE statement in my sql - which you can find some more info on "Case statement in MySQL". You could also do it differently as a SUM(CASE...END) is synonymous to a SUM(IF(...)) statement.

If you would want to do over multiple columns and rows, then you would just add the individual sums you are interested in like in:

SELECT
  SUM(CASE WHEN `18` = 'c' THEN 1 ELSE 0 END)
  + SUM(CASE WHEN `17` = 'c' THEN 1 ELSE 0 END) 
  AS cnt_18_17_c FROM badania

Different groupings (other than all) can by defined with a GROUP BY. If it is row by row - then the SUM is not even needed.

SELECT
 CASE WHEN `18` = 'c' THEN 1 ELSE 0 END
 + CASE WHEN `17` = 'c' THEN 1 ELSE 0 END
 AS cnt_18_17_c FROM badania

in the latter case, you can also user smarter techniques. You could concatenate all the tooth as a string, and count occurrences in a string, assuming that the standard set of API's with mysql offers you such a character count. - or you could potentially accomplish the count from within your programming environment (which was PHP). You can also use another smart way to do those character counts.

So I gave a couple of examples - hope I got the MySQL syntax right. Not quote sure exactly what you were asking - I suspect I might have covered it here.

Community
  • 1
  • 1
YoYo
  • 9,157
  • 8
  • 57
  • 74
-1

The query is making the assumption you want to look at a particular patient and is making a guess at how this is done - adjust accordingly. Remove the where clause if you want to aggregate all patients.

select tooth_code, count(*)
  from ( select 18 as tooth_code from badania where idb=2 and pacjent=25 union all
         select 17 from badania union where idb=2 and pacjent=25 all
         select 16 from badania union where idb=2 and pacjent=25 all
         select 15 from badania union where idb=2 and pacjent=25 all
         select 14 from badania union where idb=2 and pacjent=25 all
         select 13 from badania union where idb=2 and pacjent=25 all
         select 12 from badania union where idb=2 and pacjent=25 all
         select 11 from badania union where idb=2 and pacjent=25 all
         select 21 from badania union where idb=2 and pacjent=25 all
         select 22 from badania union where idb=2 and pacjent=25 all
         select 23 from badania union where idb=2 and pacjent=25 all
         select 24 from badania union where idb=2 and pacjent=25 all
         select 25 from badania union where idb=2 and pacjent=25 all
         select 26 from badania union where idb=2 and pacjent=25 all
         select 27 from badania union where idb=2 and pacjent=25 all
         select 28 from badania union where idb=2 and pacjent=25 all
         select 47 from badania union where idb=2 and pacjent=25 all
         select 46 from badania union where idb=2 and pacjent=25 all
         select 45 from badania union where idb=2 and pacjent=25 all
         select 44 from badania union where idb=2 and pacjent=25 all
         select 43 from badania union where idb=2 and pacjent=25 all
         select 42 from badania union where idb=2 and pacjent=25 all
         select 41 from badania union where idb=2 and pacjent=25 all
         select 31 from badania union where idb=2 and pacjent=25 all
         select 32 from badania union where idb=2 and pacjent=25 all
         select 33 from badania union where idb=2 and pacjent=25 all
         select 34 from badania where idb=2 and pacjent=25 union all
         select 35 from badania where idb=2 and pacjent=25 union all
         select 36 from badania where idb=2 and pacjent=25 union all
         select 37 from badania where idb=2 and pacjent=25 union all
         select 38 from badania where idb=2 and pacjent=25 
       ) 
   group by tooth_code
RMathis
  • 588
  • 2
  • 7