CODEWITHSUNDEEP
mysqlsql
Arthur Mayer
Arthur Mayer

Reputation: 33

Mysql query get SUM() specific row?

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)

Upvotes: 0

Views: 84

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

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;

Upvotes: 0

Matt
Matt

Reputation: 14341

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:

  • You shouldn't use implicit join syntax, explicit join has more functionality in the long run and has been a standard for more than 20 years
  • 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.
  • finally I actually calculated the range as well.

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

Upvotes: 0

Darshan Mehta
Darshan Mehta

Reputation: 30809

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.

Upvotes: 1

Related Questions