CODEWITHSUNDEEP
mysql
user2855910
user2855910

Reputation: 3

generate serial number which resets for a particular condition using mysql query

I have a table named browsekot in mysql database.This table contains menu items that have been ordered from different outlets(restaurants) along with their quantites and price during dine-in. I need to generate a report.

On using below query:

rec.Open "select Outlet,ItemName,id,sum(Quantity) as Quantity, sum(Value) as Value,@i:= @i + 1 as result from (SELECT  @i := 0) h , browsekot group by ItemName,Outlet order by @i ", adoconn

gives my this Output:

Sr.No.....Outlet.....Name
1.............Taj...........x
2.............Taj...........y
3.............Mez..........t
4.............Mez..........z

But i want to reset the count @i for each outlet and want my output to be:

Sr.No.....Outlet.....Name
1.............Taj...........x
2.............Taj...........y
1.............Mez..........t
2.............Mez..........z

I want to reset the count in the above query itself as i will be using this query later with SHAPE Command for displaying in datareport. how do i change the above query to reset the count for each outlet?

Upvotes: 0

Views: 929

Answers (1)

Barmar
Barmar

Reputation: 781721

Use the following SQL:

select Outlet,ItemName,id, Quantity, `Value`,
       @i:= IF(Outlet = @last_outlet, @i + 1, 1) as result,
       @last_outlet := Outlet
from (SELECT  @i := 0, @last_outlet := NULL) h
JOIN (SELECT Outlet, ItemName, id, SUM(Quantity) AS Quantity, SUM(`Value`) as `Value`
      FROM browsekot
      GROUP BY ItemName,Outlet
      ORDER BY Outlet) i

FIDDLE

Upvotes: 2

Related Questions