Is it possible to get specific row in query using like SUM? Example:
id tickets
1 10 1-10 10=10
2 35 11-45 10+35=45
3 45 46-90 10+35+45=90
4 110 91-200 10+35+45+110=200
Total: 200 tickets(In SUM), I need to get row ID who have ticket with number like 23(Output would be ID: 2, because ID: 2 contains 11-45tickets in SUM)
You can do it by defining a local variable into your select query (in form clause), e.g.:
select id, @total := @total + tickets as seats
from test, (select @total := 0) t
Here is the SQL Fiddle.
select id, @total+1 as lowest, @total := @total + tickets as highest from test, (select @total := 0) t will give you the range you could query with eg. between.You seem to want the row where "23" fits in. I think this does the trick:
select t.*
from (select t.*, (@total := @total + tickets) as running_total
from t cross join
(select @total := 0) params
order by id
) t
where 23 > running_total - tickets and 23 <= running_total;
SELECT
d.id
,d.tickets
,CONCAT(
TRIM(CAST(d.RunningTotal - d.tickets + 1 AS CHAR(10)))
,'-'
,TRIM(CAST(d.RunningTotal AS CHAR(10)))
) as TicketRange
,d.RunningTotal
FROM
(
SELECT
id
,tickets
,@total := @total + tickets as RunningTotal
FROM
test
CROSS JOIN (select @total := 0) var
ORDER BY
id
) d
This is similar to Darshan's answer but there are a few key differences:
ORDER BY will make a huge difference on your running total when calculated with a variable! if you change the order it will calculate differently so you need to consider how you want to do the running total, by date? by id? by??? and make sure you put it in the query.And here is how you can do it without using variables:
SELECT
d.id
,d.tickets
,CONCAT(
TRIM(d.LowRange)
,'-'
,TRIM(
CAST(RunningTotal AS CHAR(10))
)
) as TicketRange
,d.RunningTotal
FROM
(
SELECT
t.id
,t.tickets
,CAST(COALESCE(SUM(t2.tickets),0) + 1 AS CHAR(10)) as LowRange
,t.tickets + COALESCE(SUM(t2.tickets),0) as RunningTotal
FROM
test t
LEFT JOIN test t2
ON t.id > t2. id
GROUP BY
t.id
,t.tickets
) d
