MySql query : Get sum of N rows where N is defined in another column value

Question

I have the following table,

 id  |   add_card_id |   rate | category |  category_count |    counts | total
7597 |    723        |    3   |   7      |      1          |     300   | 108
7596 |    723        |    3   |   9      |      1          |     500   | 180
7594 |    723        |    3   |   11     |      1          |     400   | 240
7593 |    723        |    5   |   10     |      2          |     400   | 240
7593 |    723        |    5   |   13     |      2          |     400   | 240

I want to get sum of total(column name) where rate is same but sum include only nth no. of rows having max total, nth is defined by category_count (column name).

e.g the rate of first three column is 3 and category_ count is 1 so sum (total) first three rows is 240 because 240 > 180 > 108 and in the case of last two rows rate is 5 and category_count is 2 so 240 +240= 480 and i want 720= 240(from first three rows) + 480 (from last two rows).

I have tried the following query

SELECT SUM(total) 
FROM (SELECT MAX(total) 
      FROM tableName 
      GROUP BY rate, category_count) as newTable

it's giving me 240+ 240 so how can I give the limit by category_count(column)?

To confirm, for rate=3 you just want one row from rate=3 because category_count is one, and that it must choose the highest total (i.e. order by total desc?) — StuartLC, Dec 27 '14 at 07:13
See the answers [here](http://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group) for getting the top `N` rows per group. You just have to change it so that `N` comes from the `category_count` column. — Barmar, Dec 27 '14 at 07:19

Hrithu · Answer 1 · 2014-12-27T07:43:08.393

0

try this

select sum(cnt) from(select max(cnt)cnt from
(SELECT rate, category_count,total*count(*)cnt FROM tableName GROUP BY rate, category_count,total)temp group by rate, category_count)temp

Here My Example see the link

edited Dec 27 '14 at 07:43

answered Dec 27 '14 at 07:14

Hrithu

281
4
13

StuartLC · Accepted Answer · 2014-12-27T08:09:07.413

You can simulate ROW_NUMBER and PARTITION BY in MySql by dynamically assigning variables

SELECT rate, SUM(total) 
FROM 
(
  SELECT total, rate, category_count, 
         -- Row num counter, resets at each different rate
         @row_num := IF(@prev_rate=rate, @row_num+1, 1) AS RowNum, 
         @prev_rate := rate -- Track to see if we are in the same rate
  FROM tableName,
     (SELECT @row_num := 1) x, -- Set initial value for @row_num and @prev_rate
     (SELECT @prev_rate := '') y
  -- Important, must keep rates together, then order by your requirement
  ORDER BY rate ASC, total DESC 
) ranked
WHERE ranked.RowNum <= ranked.category_count -- Your requirement of TOP N
GROUP BY rate;

The above returns

Rate Total
3    240
5    480

And if you do the SELECT SUM(total) then drop the GROUP BY you'll get 720 as you want.

SqlFiddle here

Edit

It seems rate is defined as (I'd assumed an integer from your sample data)

`rate` varchar(X) COLLATE utf8_unicode_ci

Change the one line:

 (SELECT @prev_rate := '' COLLATE utf8_unicode_ci) y

Which will set the temporary tracking variable to the same type as your column

It's giving the following err Error Code: 1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' — Ammar Hayder Khan, Dec 27 '14 at 07:56

MySql query : Get sum of N rows where N is defined in another column value

2 Answers2