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)?
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.
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments