webdev1
-
Posts
15 -
Joined
-
Last visited
Posts posted by webdev1
-
-
Thank for your quick response Barand
The SQL statement does not run on the table structure I have:
id int
ball1 - ball6 int(2)
total 7 columns
& if I do run this I get the error #1054 - Unknown column 'a.ball' in 'field list'
-
Thank you for your help. Yes any combination of 3 columns that counts occurrences in desc order to find the most common triple sequence.
-
Using the SQL statement discussed earllier:
SELECT balls, COUNT(*) as occurenceFROM(SELECT CONCAT_WS(',', Ball1, Ball2, Ball3) as balls,Ball1 as `A`, Ball2 as `B`, Ball3 as `C`FROM randomUNION ALLSELECT CONCAT_WS(',', Ball2, Ball3, Ball4) as balls,Ball2 as `A`, Ball3 as `B`, Ball4 as `C`FROM randomUNION ALLSELECT CONCAT_WS(',', Ball3, Ball4, Ball5) as balls,Ball3 as `A`, Ball4 as `B`, Ball5 as `C`FROM randomUNION ALLSELECT CONCAT_WS(',', Ball4, Ball5, Ball6) as balls,Ball4 as `A`, Ball5 as `B`, Ball6 as `C`FROM randomUNION ALLSELECT CONCAT_WS(',', Ball5, Ball6, Ball1) as balls,Ball5 as `A`, Ball6 as `B`, Ball1 as `C`FROM random) AS tGROUP BY balls ORDER BY occurence desc LIMIT 10The highest I get is 3 but if you do a direct search for numbers 9,27,42 you will get 9 occurrencesThis proves the logic in the above SQL statement is at fault. -
Thank you for your replies.
The data sent earlier was from the wrong table.
Here is the correct data (55 rows) comma delimited rows terminated with colon :
1, 3, 5, 14, 22, 30, 44:2, 6, 12, 15, 16, 31, 44:3, 11, 17, 21, 29, 30, 40:4, 26, 35, 38, 43, 47, 49:5, 3, 5, 9, 13, 14, 38:6, 2, 3, 27, 29, 39, 44:7, 9, 17, 32, 36, 42, 44:8, 2, 5, 21, 22, 25, 32:9, 7, 17, 23, 32, 38, 42:10, 6, 16, 20, 30, 31, 47:11, 4, 16, 25, 26, 31, 43:12, 1, 7, 37, 38, 42, 46:13, 15, 18, 29, 35, 38, 48:14, 16, 19, 21, 29, 36, 45:15, 5, 8, 10, 18, 31, 33:16, 11, 12, 17, 26, 36, 42:17, 2, 13, 22, 27, 29, 46:18, 9, 18, 19, 24, 31, 41:19, 4, 17, 41, 42, 44, 49:20, 22, 25, 30, 32, 41, 43:21, 14, 17, 22, 24, 42, 47:22, 1, 4, 6, 23, 26, 49:23, 8, 18, 20, 33, 36, 38:24, 9, 15, 22, 31, 34, 48:25, 5, 14, 17, 35, 43, 48:26, 7, 16, 25, 26, 28, 41:27, 15, 16, 17, 28, 32, 46:28, 12, 13, 25, 37, 44, 45:29, 1, 21, 29, 31, 32, 40:30, 12, 15, 26, 44, 46, 49:31, 2, 3, 17, 23, 40, 43:32, 2, 3, 4, 19, 23, 40:33, 2, 3, 17, 22, 40, 42:34, 10, 18, 34, 35, 39, 48:35, 8, 10, 26, 34, 48, 49:36, 10, 12, 22, 34, 46, 48:37, 14, 18, 19, 35, 44, 45:38, 15, 19, 25, 36, 44, 45:39, 11, 17, 19, 40, 44, 45:40, 19, 20, 23, 42, 44, 45:41, 24, 25, 26, 38, 39, 40:42, 2, 3, 21, 22, 23, 40:43, 4, 14, 15, 17, 21, 31:44, 14, 15, 17, 26, 35, 46:45, 14, 15, 17, 29, 30, 33:46, 9, 10, 16, 18, 27, 42:47, 1, 5, 9, 27, 42, 45:48, 9, 16, 23, 26, 27, 42:49, 5, 9, 27, 35, 42, 43:50, 9, 27, 28, 31, 41, 42:51, 9, 10, 11, 27, 42, 47:52, 9, 21, 27, 36, 42, 48:53, 9, 25, 27, 31, 42, 45:54, 9, 27, 29, 37, 42, 44:55, 2, 17, 23, 38, 40, 49:The first number is the id. The other 6 columns is the random data columns.This data is a test data. There are lines with common triple results maximum should be 9. -
The table random contains id (int 5) & ball1, ball2, ball3, ball4, ball5, ball6 all of int(2) types. There are no varchar types.
I am using phpmyadmin (latest version) to do the SQL searches.
Here is some test data (30 rows):
3 5 14 22 30 44 106 12 15 16 31 44 3711 17 21 29 30 40 3126 35 38 43 47 49 283 5 9 13 14 38 302 3 27 29 39 44 69 17 32 36 42 44 162 5 21 22 25 32 467 17 23 32 38 42 486 16 20 30 31 47 44 16 25 26 31 43 211 7 37 38 42 46 2015 18 29 35 38 48 516 19 21 29 36 45 435 8 10 18 31 33 2811 12 17 26 36 42 132 13 22 27 29 46 369 18 19 24 31 41 214 17 41 42 44 49 2422 25 30 32 41 43 2914 17 22 24 42 47 341 4 6 23 26 49 88 18 20 33 36 38 469 15 22 31 34 48 235 14 17 35 43 48 227 16 25 26 28 41 1915 16 17 28 32 46 2212 13 25 37 44 45 91 21 29 31 32 40 2712 15 26 44 46 49 14Without conducting test I think this should have more than 1 common occurrence of 3 -
Thank you for your help.
Your expertise is appreciated.
The results are good but not accurate, there appears to be some combinations missing which I found by random search.
A group of 3 numbers occurred 10 times and the highest result I am getting with this SQL query is 7.
Is there anything missing?
-
Thank you I appreciate your help.
With this SQL query I get the following result:
balls occurence
3,5,14 5069
I wanted a list of more than one result and the occurrence appears to be incorrect as there are only 1845 rows in the table.
The table structure is as follows and Ball data is numeric 1-50:
id
Ball1
Ball2
Ball3
Ball4
Ball5
Ball6Can the SQL statement be changed to give a list like this:
balls occurence
3,5,14 99
23,34,44 55
etc
etc to find the 3 common combination occurrences and how many occurence times in sequential order by highest first
-
Thank you however I get the error:
#1248 - Every derived table must have its own alias
when running:
SELECT balls, COUNT(*) as occurence
FROM
(
SELECT CONCAT_WS(',', Ball1, Ball2, Ball3) as balls,
Ball1 as `A`, Ball2 as `B`, Ball3 as `C`
FROM random
UNION
SELECT CONCAT_WS(',', Ball2, Ball3, Ball4) as balls,
Ball2 as `A`, Ball3 as `B`, Ball4 as `C`
FROM random
UNION
SELECT CONCAT_WS(',', Ball3, Ball4, Ball5) as balls,
Ball3 as `A`, Ball4 as `B`, Ball5 as `C`
FROM random
UNION
SELECT CONCAT_WS(',', Ball4, Ball5, Ball6) as balls,
Ball4 as `A`, Ball5 as `B`, Ball6 as `C`
FROM random
) -
This SQL query appears to be dependent upon input by selecting $ball1-6 in the WHERE section.
It will not work if I use this in mysql and $ball* variable needs a number.
I want an SQL query that does the search without any input. i.e. it finds common ball number matches with more than one instance.
Is this possible?
-
Thanks I will try this query. FYI the data is in numerical order for each draw. Table columns are id, ball1, ball2, ball3, ball4, ball5, ball6.
-
Hi thanks for replying.
I have created a table populated with id & 6 columns for the random ball numbers.
The query above does a search for 3 chosen numbers to see if they exist in the database.
I want to analyse the data to find common sets of 3 number sequences and count how many times they exist without selecting the ball numbers to count.
So a sequence of say 2 4 6 may exist more than once but I would like to know how many times.
I can then use the count number for further analysis.
Is this possible in SQL?
-
Hi,
I am using php 5.4.7 & MySQL 5.5.21
I am working on a academic research project for analysing random sequences.
I am using data from a lottery as source data as these are random sequences of numbers up to 50.
I am using SQL query to do a table search on 3 random numbers (php):
SELECT id, , GROUP_CONCAT(ball ORDER BY ball) balls FROM ( SELECT id Ball1 ball FROM random UNION SELECT id,Ball2 ball FROM random UNION SELECT id, Ball3 ball FROM random UNION SELECT id, Ball4 ball FROM random UNION SELECT id, Ball5 ball FROM random UNION SELECT id, Ball6 ball FROM random ) x WHERE ball IN ( $ball1, $ball2, $ball3, $ball4, $ball5, $ball6) GROUP BY id HAVING COUNT(*) = 3
which works for 3 chosen numbers however how do I create an SQL statement to find 3 common sequences (without selecting the numbers) that occur more than once? I assume it maybe possible with mysql regex statement but do not know how to create this statement.
I hope someone can help a newbie.
Thanks in advance.
SQL help for extracting common details
in MySQL Help
Posted
THANK YOU so much.
I learnt something new.
I normalized the table and the SQL statement works.
Thank you Barand & vinny42