Total count function

PHP
1

Hi,

If I have a list as below fetched from mysql db
123
321
112
234
213
234

I want to count how many times this 1,2 and 3 numbers are displayed together regardless the positioning of the number… is there any function I can use to accomplish this. In the case above, it should able to show 3 counts as the result (123, 321 and 213)

Please advise and thanks in advance.

2 
SELECT
  somecol,
  COUNT(*)
FROM
  yourtable
WHERE
  somecol IN (123, 132, 213, 231, 312, 321)
GROUP BY
  somecol
3

sounds an awful lot like a homework problem. But Dan’s solution will work.

4

actually it sounds more like a dog’s breakfast of poor design

nope, sorry :smiley:

SELECT somecolumn
  FROM ( SELECT somecolumn
              , SUBSTRING(somecolumn FROM 1 FOR 1) AS digit
           FROM daTable
         UNION ALL
         SELECT somecolumn
              , SUBSTRING(somecolumn FROM 2 FOR 1) AS digit
           FROM daTable
         UNION ALL
         SELECT somecolumn
              , SUBSTRING(somecolumn FROM 3 FOR 1) AS digit
           FROM daTable ) AS u
GROUP
    BY somecolumn
HAVING COUNT(CASE WHEN digit = '1' 
                  THEN 'curly' END) = 1
   AND COUNT(CASE WHEN digit = '2' 
                  THEN 'larry' END) = 1
   AND COUNT(CASE WHEN digit = '3' 
                  THEN 'shemp' END) = 1
5

oooooooooh, crap

my apologies, dan, and starlion

i was wrong and you were right

and my solution sucks

:blush:

6

EDIT: Hehe. posting at the same time!

7

thanks guys,

I tried this one but why there is an error ?

SELECT 1st, 2nd, 3rd, s1, s2, s3, s4, s5, COUNT(*)
FROM
MyStory
WHERE
1st, 2nd, 3rd, s1, s2, s3, s4, s5 IN (123, 132, 213, 231, 312, 321)

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’ 2nd, 3rd, s1, s2, s3, s4, s5 IN (123, 132, 213, 231, 312, 321) L’ at line 5

Please advise.

8

you might want to consider taking an SQL tutorial :slight_smile:

9

I’m going to walk through this as the parser ‘thinks’ about it…

 SELECT `1st`, `2nd`, `3rd`, `s1`, `s2`, `s3`, `s4`, `s5`, COUNT(*) [COLOR="Red"]Okay. Bunch of fields and... a very repetitive count.[/COLOR]
FROM  MyStory [COLOR="Red"]Okay, now i know which table to use.[/COLOR]
WHERE
  `1st`, [COLOR="Red"]That's... not a logical test. what's a comma doing here?[/COLOR]
`2nd`, [COLOR="Red"]Same.[/COLOR]
`3rd`, [COLOR="Red"]Same.[/COLOR]
`s1`, [COLOR="Red"]Same.[/COLOR]
`s2`, [COLOR="Red"]Same.[/COLOR]
`s3`, [COLOR="Red"]Same.[/COLOR]
`s4`, [COLOR="Red"]Same.[/COLOR]
`s5` IN (123, 132, 213, 231, 312, 321) [COLOR="Red"]Hey, this one is actually a test![/COLOR]

Simply put, you have to rewrite your WHERE clause using actual logical tests (even if it’s the same test over and over) and either AND or OR between the cases.

10

I thought I need to put the column names as recommended ?

SELECT
somecol,
COUNT(*)
FROM
yourtable
WHERE
somecol IN (123, 132, 213, 231, 312, 321)

but if I remove the column name and comma, the query will be incomplete…

11

if you want to run the test on each column, copy the logical test you have on s5, put it on each field, and join them together with OR’s instead of commas.

12

Previous replies dealt with only fetching the correct items from your db, if you have to analyse a result set in PHP this might help.

$test = array( 123, 321, 112, 234, 213, 234 );

$tgt = array(1,2,3);

foreach( $test as $item){
$res = '';
  foreach( $tgt as $v ){
  $res .= strpos($item, (string)$v);
  }
if (count( $tgt ) == strlen($res) )
   echo $item .  '<br>' ;
}

//123
//321
//213

This can of course be improved upon, but it works for 1123 etc

13 

SELECT `1st`, `2nd`, `3rd`, `s1`, `s2`, `s3`, `s4`, `s5`, COUNT(*)
FROM
MyStory
WHERE
`1st`, `2nd`, `3rd`, `s1`, `s2`, `s3`, `s4`, `s5` IN (123, 132, 213, 231, 312, 321)

I am trying to use the query above. Can some guru tell me is there anyway I can make the query so it will count all records with 3 digits posted? e.g : 123 so no matter how these numbers are positioned, it will still be counted.

Sorry for asking such a noob question. I did try to search online but couldn’t find the solution.

14

Just this once :stuck_out_tongue: I’m assuming you want to search for the three digit combination being the value of any field selected.


SELECT `1st`, `2nd`, `3rd`, `s1`, `s2`, `s3`, `s4`, `s5`, COUNT(*)
FROM
MyStory
WHERE
`1st` IN (123, 132, 213, 231, 312, 321)
OR `2nd` IN (123, 132, 213, 231, 312, 321)
OR `3rd` IN (123, 132, 213, 231, 312, 321)
OR `s1` IN (123, 132, 213, 231, 312, 321)
OR `s2` IN (123, 132, 213, 231, 312, 321)
OR `s3` IN (123, 132, 213, 231, 312, 321)
OR `s4` IN (123, 132, 213, 231, 312, 321)
OR `s5` IN (123, 132, 213, 231, 312, 321)

(and if you only want the count out of there, make your select just “SELECT COUNT(s1)” with none of the other fields selected.)

15

thanks for the guidance but I am looking to run a query to search all the possible combination in from 000 to 999. There anyway I can make it to automatically search all and count for me?

16

I think I need a bit more understanding of what you want before I can answer further. All possible combinations of… what? 0-9 in 3 digit sets?

17

yeah, I have a table with all random 3 digits number… So now what I need to do is run a query to count how many times the 3 digits have appeared together in the db. I just need to trace all possible combination from 000 to 999. If there are records in db with numbers like 123, 213, 132, this will be counted as 3 for 123. I am cracking my head to figure this out and hopefully some guru can assist on this.thanks

18

I had an idea. You can match any value having the same 3 digits, in any order, by adding up those digits (e.g. 1 + 2 + 3 = 2 + 1 + 3 = 1 + 3 + 2).

For example, if you want all rows where s5 contains the digits 1,2,3 (which add up to 6):

SELECT
  s5
FROM
  MyStory
WHERE
  floor((s5 - (s5 % 100)) / 100) + floor((s5 % 100) / 10) + s5 % 10 = 6

floor((num - (num % 100)) / 100) = 100s digit

floor((num % 100) / 10) = 10s digit

num % 10 = 1s digit

To do it for all possible combinations, GROUP BY that expression to get row counts for each sum of digits.

But that doesn’t solve the problem, since 501 also adds up to 6.

Should give you ideas though, now that you know you can isolate the digits with basic algebra.

19

It’s gonna get ugly in a hurry; I like these sorts of problems. Here’s how i’d do it in a PHP/MySQL combination. And i’m probably doing it wrong, so one of the mySQL gurus will correct me.

PS: Which field is holding the combination you’re searching for? I’m going to assume s5 for my example.


//Define combinations.
for($i = 0; $i <= 9; $i++) {
  for($j = 0; $j <= 9; $j++) {
    for($k = 0; $k <= 9; $k++) {
       $key = array($i,$j,$k);
       sort($key);
       $combs[implode('',$key)][] = $i.$j.$k;
     }
   }
}
//Generate absolutely massive SQL query.
$sql = "SELECT t1.comb,COUNT(t1.comb) FROM (SELECT CASE ";
foreach($combs AS $key => $comb) {
 $sql .= "WHEN s5 IN('".implode("','",$comb)."') THEN ".$key." ";
}
$sql .= " END CASE AS comb FROM table) AS t1 GROUP BY t1.comb ORDER BY t1.comb;";
20

and for those of you who feel the need to be suicidal in reading the expanded query, here’s what my script generates:


SELECT t1.comb,COUNT(t1.comb) 
FROM (
SELECT CASE WHEN s5 IN('000') THEN 000 
WHEN s5 IN('001','010','100') THEN 001 
WHEN s5 IN('002','020','200') THEN 002 
WHEN s5 IN('003','030','300') THEN 003 
WHEN s5 IN('004','040','400') THEN 004 
WHEN s5 IN('005','050','500') THEN 005 
WHEN s5 IN('006','060','600') THEN 006 
WHEN s5 IN('007','070','700') THEN 007 
WHEN s5 IN('008','080','800') THEN 008 
WHEN s5 IN('009','090','900') THEN 009 
WHEN s5 IN('011','101','110') THEN 011 
WHEN s5 IN('012','021','102','120','201','210') THEN 012 
WHEN s5 IN('013','031','103','130','301','310') THEN 013 
WHEN s5 IN('014','041','104','140','401','410') THEN 014 
WHEN s5 IN('015','051','105','150','501','510') THEN 015 
WHEN s5 IN('016','061','106','160','601','610') THEN 016 
WHEN s5 IN('017','071','107','170','701','710') THEN 017 
WHEN s5 IN('018','081','108','180','801','810') THEN 018 
WHEN s5 IN('019','091','109','190','901','910') THEN 019 
WHEN s5 IN('022','202','220') THEN 022 
WHEN s5 IN('023','032','203','230','302','320') THEN 023 
WHEN s5 IN('024','042','204','240','402','420') THEN 024 
WHEN s5 IN('025','052','205','250','502','520') THEN 025 
WHEN s5 IN('026','062','206','260','602','620') THEN 026 
WHEN s5 IN('027','072','207','270','702','720') THEN 027 
WHEN s5 IN('028','082','208','280','802','820') THEN 028 
WHEN s5 IN('029','092','209','290','902','920') THEN 029 
WHEN s5 IN('033','303','330') THEN 033 
WHEN s5 IN('034','043','304','340','403','430') THEN 034 
WHEN s5 IN('035','053','305','350','503','530') THEN 035 
WHEN s5 IN('036','063','306','360','603','630') THEN 036 
WHEN s5 IN('037','073','307','370','703','730') THEN 037 
WHEN s5 IN('038','083','308','380','803','830') THEN 038 
WHEN s5 IN('039','093','309','390','903','930') THEN 039 
WHEN s5 IN('044','404','440') THEN 044 
WHEN s5 IN('045','054','405','450','504','540') THEN 045 
WHEN s5 IN('046','064','406','460','604','640') THEN 046 
WHEN s5 IN('047','074','407','470','704','740') THEN 047 
WHEN s5 IN('048','084','408','480','804','840') THEN 048 
WHEN s5 IN('049','094','409','490','904','940') THEN 049 
WHEN s5 IN('055','505','550') THEN 055 
WHEN s5 IN('056','065','506','560','605','650') THEN 056 
WHEN s5 IN('057','075','507','570','705','750') THEN 057 
WHEN s5 IN('058','085','508','580','805','850') THEN 058 
WHEN s5 IN('059','095','509','590','905','950') THEN 059 
WHEN s5 IN('066','606','660') THEN 066 
WHEN s5 IN('067','076','607','670','706','760') THEN 067 
WHEN s5 IN('068','086','608','680','806','860') THEN 068 
WHEN s5 IN('069','096','609','690','906','960') THEN 069 
WHEN s5 IN('077','707','770') THEN 077 
WHEN s5 IN('078','087','708','780','807','870') THEN 078 
WHEN s5 IN('079','097','709','790','907','970') THEN 079 
WHEN s5 IN('088','808','880') THEN 088 
WHEN s5 IN('089','098','809','890','908','980') THEN 089 
WHEN s5 IN('099','909','990') THEN 099 
WHEN s5 IN('111') THEN 111 
WHEN s5 IN('112','121','211') THEN 112 
WHEN s5 IN('113','131','311') THEN 113 
WHEN s5 IN('114','141','411') THEN 114 
WHEN s5 IN('115','151','511') THEN 115 
WHEN s5 IN('116','161','611') THEN 116 
WHEN s5 IN('117','171','711') THEN 117 
WHEN s5 IN('118','181','811') THEN 118 
WHEN s5 IN('119','191','911') THEN 119 
WHEN s5 IN('122','212','221') THEN 122 
WHEN s5 IN('123','132','213','231','312','321') THEN 123 
WHEN s5 IN('124','142','214','241','412','421') THEN 124 
WHEN s5 IN('125','152','215','251','512','521') THEN 125 
WHEN s5 IN('126','162','216','261','612','621') THEN 126 
WHEN s5 IN('127','172','217','271','712','721') THEN 127 
WHEN s5 IN('128','182','218','281','812','821') THEN 128 
WHEN s5 IN('129','192','219','291','912','921') THEN 129 
WHEN s5 IN('133','313','331') THEN 133 
WHEN s5 IN('134','143','314','341','413','431') THEN 134 
WHEN s5 IN('135','153','315','351','513','531') THEN 135 
WHEN s5 IN('136','163','316','361','613','631') THEN 136 
WHEN s5 IN('137','173','317','371','713','731') THEN 137 
WHEN s5 IN('138','183','318','381','813','831') THEN 138 
WHEN s5 IN('139','193','319','391','913','931') THEN 139 
WHEN s5 IN('144','414','441') THEN 144 
WHEN s5 IN('145','154','415','451','514','541') THEN 145 
WHEN s5 IN('146','164','416','461','614','641') THEN 146 
WHEN s5 IN('147','174','417','471','714','741') THEN 147 
WHEN s5 IN('148','184','418','481','814','841') THEN 148 
WHEN s5 IN('149','194','419','491','914','941') THEN 149 
WHEN s5 IN('155','515','551') THEN 155 
WHEN s5 IN('156','165','516','561','615','651') THEN 156 
WHEN s5 IN('157','175','517','571','715','751') THEN 157 
WHEN s5 IN('158','185','518','581','815','851') THEN 158 
WHEN s5 IN('159','195','519','591','915','951') THEN 159 
WHEN s5 IN('166','616','661') THEN 166 
WHEN s5 IN('167','176','617','671','716','761') THEN 167 
WHEN s5 IN('168','186','618','681','816','861') THEN 168 
WHEN s5 IN('169','196','619','691','916','961') THEN 169 
WHEN s5 IN('177','717','771') THEN 177 
WHEN s5 IN('178','187','718','781','817','871') THEN 178 
WHEN s5 IN('179','197','719','791','917','971') THEN 179 
WHEN s5 IN('188','818','881') THEN 188 
WHEN s5 IN('189','198','819','891','918','981') THEN 189 
WHEN s5 IN('199','919','991') THEN 199 
WHEN s5 IN('222') THEN 222 
WHEN s5 IN('223','232','322') THEN 223 
WHEN s5 IN('224','242','422') THEN 224 
WHEN s5 IN('225','252','522') THEN 225 
WHEN s5 IN('226','262','622') THEN 226 
WHEN s5 IN('227','272','722') THEN 227 
WHEN s5 IN('228','282','822') THEN 228 
WHEN s5 IN('229','292','922') THEN 229 
WHEN s5 IN('233','323','332') THEN 233 
WHEN s5 IN('234','243','324','342','423','432') THEN 234 
WHEN s5 IN('235','253','325','352','523','532') THEN 235 
WHEN s5 IN('236','263','326','362','623','632') THEN 236 
WHEN s5 IN('237','273','327','372','723','732') THEN 237 
WHEN s5 IN('238','283','328','382','823','832') THEN 238 
WHEN s5 IN('239','293','329','392','923','932') THEN 239 
WHEN s5 IN('244','424','442') THEN 244 
WHEN s5 IN('245','254','425','452','524','542') THEN 245 
WHEN s5 IN('246','264','426','462','624','642') THEN 246 
WHEN s5 IN('247','274','427','472','724','742') THEN 247 
WHEN s5 IN('248','284','428','482','824','842') THEN 248 
WHEN s5 IN('249','294','429','492','924','942') THEN 249 
WHEN s5 IN('255','525','552') THEN 255 
WHEN s5 IN('256','265','526','562','625','652') THEN 256 
WHEN s5 IN('257','275','527','572','725','752') THEN 257 
WHEN s5 IN('258','285','528','582','825','852') THEN 258 
WHEN s5 IN('259','295','529','592','925','952') THEN 259 
WHEN s5 IN('266','626','662') THEN 266 
WHEN s5 IN('267','276','627','672','726','762') THEN 267 
WHEN s5 IN('268','286','628','682','826','862') THEN 268 
WHEN s5 IN('269','296','629','692','926','962') THEN 269 
WHEN s5 IN('277','727','772') THEN 277 
WHEN s5 IN('278','287','728','782','827','872') THEN 278 
WHEN s5 IN('279','297','729','792','927','972') THEN 279 
WHEN s5 IN('288','828','882') THEN 288 
WHEN s5 IN('289','298','829','892','928','982') THEN 289 
WHEN s5 IN('299','929','992') THEN 299 
WHEN s5 IN('333') THEN 333 
WHEN s5 IN('334','343','433') THEN 334 
WHEN s5 IN('335','353','533') THEN 335 
WHEN s5 IN('336','363','633') THEN 336 
WHEN s5 IN('337','373','733') THEN 337 
WHEN s5 IN('338','383','833') THEN 338 
WHEN s5 IN('339','393','933') THEN 339 
WHEN s5 IN('344','434','443') THEN 344 
WHEN s5 IN('345','354','435','453','534','543') THEN 345 
WHEN s5 IN('346','364','436','463','634','643') THEN 346 
WHEN s5 IN('347','374','437','473','734','743') THEN 347 
WHEN s5 IN('348','384','438','483','834','843') THEN 348 
WHEN s5 IN('349','394','439','493','934','943') THEN 349 
WHEN s5 IN('355','535','553') THEN 355 
WHEN s5 IN('356','365','536','563','635','653') THEN 356 
WHEN s5 IN('357','375','537','573','735','753') THEN 357 
WHEN s5 IN('358','385','538','583','835','853') THEN 358 
WHEN s5 IN('359','395','539','593','935','953') THEN 359 
WHEN s5 IN('366','636','663') THEN 366 
WHEN s5 IN('367','376','637','673','736','763') THEN 367 
WHEN s5 IN('368','386','638','683','836','863') THEN 368 
WHEN s5 IN('369','396','639','693','936','963') THEN 369 
WHEN s5 IN('377','737','773') THEN 377 
WHEN s5 IN('378','387','738','783','837','873') THEN 378 
WHEN s5 IN('379','397','739','793','937','973') THEN 379 
WHEN s5 IN('388','838','883') THEN 388 
WHEN s5 IN('389','398','839','893','938','983') THEN 389 
WHEN s5 IN('399','939','993') THEN 399 
WHEN s5 IN('444') THEN 444 
WHEN s5 IN('445','454','544') THEN 445 
WHEN s5 IN('446','464','644') THEN 446 
WHEN s5 IN('447','474','744') THEN 447 
WHEN s5 IN('448','484','844') THEN 448 
WHEN s5 IN('449','494','944') THEN 449 
WHEN s5 IN('455','545','554') THEN 455 
WHEN s5 IN('456','465','546','564','645','654') THEN 456 
WHEN s5 IN('457','475','547','574','745','754') THEN 457 
WHEN s5 IN('458','485','548','584','845','854') THEN 458 
WHEN s5 IN('459','495','549','594','945','954') THEN 459 
WHEN s5 IN('466','646','664') THEN 466 
WHEN s5 IN('467','476','647','674','746','764') THEN 467 
WHEN s5 IN('468','486','648','684','846','864') THEN 468 
WHEN s5 IN('469','496','649','694','946','964') THEN 469 
WHEN s5 IN('477','747','774') THEN 477 
WHEN s5 IN('478','487','748','784','847','874') THEN 478 
WHEN s5 IN('479','497','749','794','947','974') THEN 479 
WHEN s5 IN('488','848','884') THEN 488 
WHEN s5 IN('489','498','849','894','948','984') THEN 489 
WHEN s5 IN('499','949','994') THEN 499 
WHEN s5 IN('555') THEN 555 
WHEN s5 IN('556','565','655') THEN 556 
WHEN s5 IN('557','575','755') THEN 557 
WHEN s5 IN('558','585','855') THEN 558 
WHEN s5 IN('559','595','955') THEN 559 
WHEN s5 IN('566','656','665') THEN 566 
WHEN s5 IN('567','576','657','675','756','765') THEN 567 
WHEN s5 IN('568','586','658','685','856','865') THEN 568 
WHEN s5 IN('569','596','659','695','956','965') THEN 569 
WHEN s5 IN('577','757','775') THEN 577 
WHEN s5 IN('578','587','758','785','857','875') THEN 578 
WHEN s5 IN('579','597','759','795','957','975') THEN 579 
WHEN s5 IN('588','858','885') THEN 588 
WHEN s5 IN('589','598','859','895','958','985') THEN 589 
WHEN s5 IN('599','959','995') THEN 599 
WHEN s5 IN('666') THEN 666 
WHEN s5 IN('667','676','766') THEN 667 
WHEN s5 IN('668','686','866') THEN 668 
WHEN s5 IN('669','696','966') THEN 669 
WHEN s5 IN('677','767','776') THEN 677 
WHEN s5 IN('678','687','768','786','867','876') THEN 678 
WHEN s5 IN('679','697','769','796','967','976') THEN 679 
WHEN s5 IN('688','868','886') THEN 688 
WHEN s5 IN('689','698','869','896','968','986') THEN 689 
WHEN s5 IN('699','969','996') THEN 699 
WHEN s5 IN('777') THEN 777 
WHEN s5 IN('778','787','877') THEN 778 
WHEN s5 IN('779','797','977') THEN 779 
WHEN s5 IN('788','878','887') THEN 788 
WHEN s5 IN('789','798','879','897','978','987') THEN 789 
WHEN s5 IN('799','979','997') THEN 799 
WHEN s5 IN('888') THEN 888 
WHEN s5 IN('889','898','988') THEN 889 
WHEN s5 IN('899','989','998') THEN 899 
WHEN s5 IN('999') THEN 999 
 END CASE AS comb FROM table) AS t1 
GROUP BY t1.comb 
ORDER BY t1.comb;