CODEWITHSUNDEEP
mysqlcountercalculated-columns
Uwe Ziegenhagen
Uwe Ziegenhagen

Reputation: 694

Include an additional counter in the MySQL result set

Can I include an additional counter in a MySQL result set? I have the following query which gives me two columns back. I need an additional column (only in the result) indicating the row of each line in the result set.

select orderid, round(sum(unitprice * quantity),2) as value
from order_details
group by orderid
order by 2 desc
limit 10

I need something like the following:

10865 1 17250.00
11030 2 16321.90
10981 3 15810.00
10372 4 12281.20
10424 5 11493.20

Upvotes: 4

Views: 14367

Answers (3)

Uwe Ziegenhagen
Uwe Ziegenhagen

Reputation: 694

Based on the two answers I managed to get the following:

SET @counter = 0; 

Select sub.orderid,sub.value,(@counter := @counter +1) as counter
FROM
(
    select orderid, 
      round(sum(unitprice * quantity),2) as value
    from order_details
    group by orderid
) sub
order by 2 desc
limit 10

The original answers showed the IDs from the inner query resulting in larger ints with huge gaps. Using the modification I get just the '1 to x' range that I need for my pgfplots LaTeX plot.

Upvotes: 0

jsist
jsist

Reputation: 5253

Try following

SET @counter = 0;
select orderid, (@counter:= @counter + 1) as counter, round(sum(unitprice * quantity),2) as value
from order_details
group by orderid
order by 3 desc
limit 10

Hope it helps...

Upvotes: 5

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

Try this:

SET @counter = 0; 
Select sub.*
FROM
(
    select orderid, (@counter := @counter +1) as counter,
      round(sum(unitprice * quantity),2) as value
    from order_details
    group by orderid
) sub
order by 2 desc

Upvotes: 11

Related Questions