# Total count function

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)

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

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

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

nope, sorry

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

oooooooooh, crap

my apologies, dan, and starlion

i was wrong and you were right

and my solution sucks

EDIT: Hehe. posting at the same time!

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

you might want to consider taking an SQL tutorial

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.

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…

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.

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

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.

Just this once 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.)

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?

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?

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

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.

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;";

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;