-1

I am trying to extract a sum of modified history field limited by 100 and grouped by each account, but the query below extracts sum of all rows from CDR_Accounts with charged_quantity = 60 and does not limit to 100.

table1 = table2 and these are the temporary tables. I have simplified the query and filtered all data in temporary tables.

Nevertheless, the query still gives me the total sum of all 'history' fields, however I need the sum for only 100 of them.

table1/table2 format:

+-----------+----------+------------+-------------+----------------------+
| i_account | id       | account_id | CLD         | history              |
+-----------+----------+------------+-------------+----------------------+
|     10272 | 46479968 | 0814781293 | 27815212963 | +1x60@1.32d100%=1.32 |
|      6316 | 46480100 | 0813741427 | 27780233136 | +1x60@1.32d100%=1.32 |
|      6316 | 46480107 | 0813741427 | 27780233136 | +1x60@1.32d100%=1.32 |
|     13830 | 46480435 | 0814346396 | 27781356515 | +1x60@1.32d100%=1.32 |
|     13830 | 46480436 | 0814346396 | 27781356515 | +1x60@1.32d100%=1.32 |
+-----------+----------+------------+-------------+----------------------+

Account

SELECT sum(SUBSTRING_INDEX(history,'=',-1)),
                 cdr.i_account,
                 cdr.account_id
         FROM table1 cdr
           WHERE cdr.id IN
               (SELECT SUBSTRING_INDEX(group_concat(s.id SEPARATOR ','), ',', 100) id
                FROM table2 s
                GROUP BY id
                )
                  GROUP BY i_account;
Vadym
  • 1
  • 4
  • @Tim Biegeleisen The purpose is to limit to 100, does not matter whether to use 'limit' or not – Vadym Oct 18 '16 at 15:14
  • I don't think group_concat can be used the way you are trying. group_concat creates a comma separated string. So the beginning of your where looks like this: `WHERE '60' IN ('10,20,40,60')`. You can probably change that subselect to `(SELECT charged_quantity FROM CDR_Accounts GROUP BY i_account LIMIT 100)` – CptMisery Oct 18 '16 at 16:26
  • @CptMisery limit in subquery is not possible in mysql. Also, I have modified the query, but it still gives me not expected result – Vadym Oct 19 '16 at 07:35
  • Ah. Sorry. I was thinking about subqueries as a join (ie `JOIN (SELECT id FROM table LIMIT 10) AS x ON`). – CptMisery Oct 19 '16 at 17:50

1 Answers1

0

I'm not clear about what your query does is. However limit applies to the final result which includes the grouping as well. if you need to limit the result of GROUP_CONCAT, first apply the limit and then apply the grouping