Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following selection string:

SELECT * , AVG( `q1` ) AS betavg, COUNT( bet_id ) AS nr, MAX( q1 ) AS qmax, 
(MAX( q1 ) - AVG( `q1` )) AS diff FROM jos_bet_1x3_best GROUP BY `bet_id`
HAVING ( COUNT( `bet_id` ) >2 AND betavg <= q1
AND (qmax - MIN( q1 )) < 0.5 AND q1 <3)
ORDER BY `diff` DESC LIMIT 0 , 30

Can I optimize this somehow ? I want to select that data where the q1 is bigger than the average.

This is the sample data:

+-------+---------------+------+------+------+---------------------+-----------+
| id    | bet_id        | q1   | qx   | q2   | timestamp           | bookie_id |
+-------+---------------+------+------+------+---------------------+-----------+
| 32665 | 4e371a6e23735 | 1.90 | 3.50 | 3.80 | 2011-08-01 21:28:14 |         4 |
| 33410 | 4e371a6e23735 | 2.02 | 3.25 | 3.70 | 2011-08-01 21:28:31 |         8 |
| 34423 | 4e371a6e23735 | 2.05 | 3.25 | 3.25 | 2011-08-01 21:30:08 |         3 |
| 32666 | 4e371a6e23fe8 | 2.20 | 3.25 | 3.20 | 2011-08-01 21:28:14 |         4 |
| 33407 | 4e371a6e23fe8 | 2.17 | 3.20 | 3.35 | 2011-08-01 21:28:31 |         8 |
| 34252 | 4e371a6e23fe8 | 2.20 | 3.25 | 3.25 | 2011-08-01 21:28:52 |         2 |
| 34418 | 4e371a6e23fe8 | 2.05 | 3.25 | 3.30 | 2011-08-01 21:30:08 |         3 |
| 32667 | 4e371a6e24750 | 1.40 | 4.25 | 8.00 | 2011-08-01 21:28:14 |         4 |
| 33406 | 4e371a6e24750 | 1.45 | 4.10 | 7.10 | 2011-08-01 21:28:31 |         8 |
| 34253 | 4e371a6e24750 | 1.40 | 4.20 | 8.50 | 2011-08-01 21:28:52 |         2 |
| 34425 | 4e371a6e24750 | 1.45 | 3.75 | 7.00 | 2011-08-01 21:30:08 |         3 |
| 32668 | 4e371a6e24ee4 | 1.80 | 3.50 | 4.25 | 2011-08-01 21:28:14 |         4 |
| 33462 | 4e371a6e24ee4 | 1.87 | 3.35 | 4.15 | 2011-08-01 21:28:31 |         8 |
| 34251 | 4e371a6e24ee4 | 1.75 | 3.55 | 4.65 | 2011-08-01 21:28:52 |         2 |
| 34420 | 4e371a6e24ee4 | 1.85 | 3.25 | 4.00 | 2011-08-01 21:30:08 |         3 |
+-------+---------------+------+------+------+---------------------+-----------+

Finally, I made an other solution based on drex solution:

SELECT A. * , ( A.q1 - tb1.betavg) AS diff, tb1.betavg, tb1.nr FROM jos_bet_1x3_best A 
    INNER JOIN (
        SELECT bet_id, AVG( `q1` ) AS betavg, COUNT( bet_id ) AS nr
        FROM jos_bet_1x3_best GROUP BY `bet_id`
    ) AS tb1
USING ( bet_id ) WHERE tb1.nr >2 AND (A.q1 - tb1.betavg) >0
ORDER BY diff DESC LIMIT 0 , 30
share|improve this question
    
I had 'BB.*,AA.q1' which weas wrong. I flipped it to 'AA.*,BB.q1'. Please try again !!! –  RolandoMySQLDBA Aug 2 '11 at 10:34
    
Updated it again. Try again, please !!! –  RolandoMySQLDBA Aug 2 '11 at 10:53
    
Question : Does your original work ??? –  RolandoMySQLDBA Aug 2 '11 at 11:02
    
Yes, it worked. –  run Aug 2 '11 at 11:11
    
Question : How many rows should come back ??? I am only getting one row. –  RolandoMySQLDBA Aug 2 '11 at 11:29
add comment

2 Answers

up vote 3 down vote accepted
SELECT A.*
FROM    jos_bet_1x3_best A
   INNER JOIN
      (SELECT bet_id,
              q1,
              AVG(`q1`) AS betavg,
              COUNT(bet_id) AS nr,
              MAX(q1) AS qmax,
              (MAX(q1) - AVG(`q1`)) AS diff
         FROM jos_bet_1x3_best
       GROUP BY `bet_id`
       HAVING (q1 >= betavg AND nr > 2 AND diff < .5 AND q1 < 3)
        LIMIT 0, 30) AS tb1
   USING (bet_id)
GROUP BY `bet_id`;

Please try this.

share|improve this answer
    
I have to admit but THIS IS THE CORRECT ANSWER !!! –  RolandoMySQLDBA Aug 2 '11 at 11:42
    
You get a +1 from me !!! –  RolandoMySQLDBA Aug 2 '11 at 11:42
    
I did change your answer a little so that q1 does not appear in the display twice. –  RolandoMySQLDBA Aug 2 '11 at 11:43
    
Otherwise it is totally correct. iUngi, please try drex's query. –  RolandoMySQLDBA Aug 2 '11 at 11:44
    
Thank you!! I'm just not sure if yours is faster than mine.. i haven't tested it yet thoroughly. –  drex Aug 2 '11 at 11:45
show 2 more comments
ALTER TABLE jos_bet_1x3_best ADD INDEX (bet_id,id);
ALTER TABLE jos_bet_1x3_best ADD INDEX (bet_id,q1);
SELECT A.*
FROM
(
    SELECT AA.*,BB.q1 FROM
    (
        SELECT
            bet_id,
            COUNT(1) nr, 
            MAX(q1) qmax,
            AVG(q1) betavg,
            MAX(q1)-AVG(q1) diff
        FROM
            (SELECT bet_id,q1 FROM jos_bet_1x3_best) AAA
        GROUP BY
            bet_id
        HAVING
            (COUNT(1) > 2 AND betavg <= q1 AND
            (qmax-MIN(q1)) < 0.5 AND q1 < 3)
        ORDER BY
            diff DESC LIMIT 0,30
    ) AA INNER JOIN
    (
        SELECT bet_id,q1
        FROM jos_bet_1x3_best
    ) BB USING(bet_id)
) A INNER JOIN jos_bet_1x3_best USING (bet_id,q1);

Not sure if this will speed things up, but give it a try anyway !!!

share|improve this answer
    
I tried your version, but I'm getting the following error: ERROR 1054 (42S22): Unknown column 'q1' in 'having clause' –  run Aug 2 '11 at 7:45
    
I'm still getting the same error. –  run Aug 2 '11 at 10:43
    
Still the same, not working. –  run Aug 2 '11 at 10:58
    
If it's help, this is the database, with data: sharetext.org/AZWV –  run Aug 2 '11 at 11:02
    
Looks like drex's query is correct !!! –  RolandoMySQLDBA Aug 2 '11 at 11:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.