I have a table with these values. I want to query this table with limit of 5 but i want total row always to be part of resultset.
Table Description:
id desc value
1 A 100
2 B 200
3 C 300
4 D 400
5 E 500
6 F 600
7 G 700
8 H 800
9 I 900
10 Total 1000
i want to know whether its possible.
That's a little messy in a single query. Maybe you can use a UNION query:
SELECT `id`, `value` FROM `table` LIMIT 5
UNION
SELECT 'Total', SUM(`value`) AS `value` FROM `table`
This will yield 5 rows from the table and a 'Total' row under.
Like this:
SELECT id, `desc`, value FROM table
UNION ALL
SELECT MAX(id), 'Total', SUM(value) FROM table;
However, If you need to limit the selection from the table to only 5, you have to use LIMIT inside two subqueries like so:
SELECT id, `desc`, value
FROM
(
SELECT id, `desc`, value FROM table1
ORDER BY id
LIMIT 5
) t
UNION ALL
SELECT MAX(id), 'Total', SUM(value)
FROM
(
SELECT id, `desc`, value FROM table1
ORDER BY id
LIMIT 5
) t;
For your sample data this will give you:
| ID | DESC | VALUE |
----------------------
| 1 | A | 100 |
| 2 | B | 200 |
| 3 | C | 300 |
| 4 | D | 400 |
| 5 | E | 500 |
| 5 | Total | 1500 |
Note that: The Total row will be the sum of all the previous value values, however, in your sample data it is not the total.
you can alternatively use WITH ROLLUP. but the downside is one column is missing: the ID.
SELECT COALESCE(`desc`, 'TOTAL') `desc`, SUM(`value`) Value
FROM Description
GROUP BY `desc`
WITH ROLLUP