Get total for column in sqlite select

Question

Want to select 3 item with values by random and show its total at the end

Example:

A  diner 2
B  lunch 3
C  diner 4
D  lunch 5

I want result for diner like:

A 2
C 4
Total 6

SELECT *, SUM(tableName.colomnNumberQnt) as Total from tableName WHERE tableName.colomnNameDietAction = "diner"; — Arwy Shelke, Dec 17 '19 at 08:15
please include your database table to your post for better understanding. — Mak-Mak Basaya, Dec 17 '19 at 08:19
Check out this link. [link](https://stackoverflow.com/a/4329492/3409734), this may help you with **random row selection**. — SimplyProgrammer, Dec 17 '19 at 11:19

score 0 · Answer 1 · edited Dec 17 '19 at 09:17

0

You can try the following:

STRING QUERY = SELECT SUM(COLUMN_NAME_QUANTITY) FROM TABLENAME WHERE COLUMN_NAME_TIME_OF_MEAL = "diner"

After executing this query, it should return the total for diner.

edited Dec 17 '19 at 09:17

iamdanchiv

4,052
4
37
42

answered Dec 17 '19 at 08:34

Salman

9
4

score 0 · Answer 2 · answered Dec 17 '19 at 16:59

This is quite tricky because it involves persisting the random rows to perform aggregation.
So I use a CTE that returns the random rows along with a dummy row containing the string 'Total' and a value of 0 for Col3.
Finally with CASE the code returns either Col3, if the row is one of the random rows or the sum of all the values in Col3 if the row is the Total row:

with cte as ( 
  select * from (
    select 0 sortorder, col1, col3
    from tablename
    where col2 = 'diner'
    order by random()
    limit 2
  )  
  union all
  select 1, 'Total', 0
)
select col1,
  case
    when sortorder = 0 then col3
    else sum(col3) over()
  end Total
from cte
order by sortorder

See the demo (I added more rows to your sample data).

Get total for column in sqlite select

2 Answers2