MySQL $i++ equivalent

Question

Query I'm using:

SELECT COUNT(*),
    SUM(amount) AS amount,
    FROM_UNIXTIME(added, '%W (%e/%m)') AS daily
FROM affiliates_earnings
WHERE added >= '1319074836'
    AND added <= '1319679636'
GROUP BY daily

Output:

+----------+--------+-------------------+
| COUNT(*) | amount | daily             |
+----------+--------+-------------------+
|        1 |    195 | Tuesday (25/10)   |
|        4 |    470 | Wednesday (26/10) |
+----------+--------+-------------------+

What I would like to show up:

+-------+----------+--------+-------------------+
| i     |COUNT(*)  | amount | daily             |
+-------+----------+--------+-------------------+
| 1     |        1 |    195 | Tuesday (25/10)   |
| 2     |        4 |    470 | Wednesday (26/10) |
+-------+----------+--------+-------------------+

Essentially i would be the sequential number (i.e. 1,2,3,4,5,6...).

How would I accomplish this?

Why do you want to do this? Iterate in your target environment when you pull from the recordset. — Lightness Races in Orbit, Oct 27 '11 at 02:09
Check this posting: http://stackoverflow.com/questions/304461/generate-an-integer-sequence-in-mysql — Raihan, Oct 27 '11 at 02:19
@MeisamMulla: That doesn't answer my question at all, nor does it preclude heeding my advice! — Lightness Races in Orbit, Oct 27 '11 at 02:33
@TomalakGeret'kal basically I'm trying to generate the following using smarty: [i, value], [i, value], [i, value] etc. Is there a better way of doing this? — Meisam Mulla, Oct 27 '11 at 02:52

score 4 · Accepted Answer · edited May 23 '17 at 11:43

4

Without ROW_NUMBER(), you can fake it per this answer or this one:

  SELECT @i := @i + 1 AS i, COUNT(*), ...
    FROM affiliates_earnings
    JOIN (SELECT @i := 0) dummy
   WHERE ...
GROUP BY ...;

edited May 23 '17 at 11:43

Community

1
1

answered Oct 27 '11 at 02:23

pilcrow

56,591
13
94
135

score 2 · Answer 2 · answered Oct 27 '11 at 02:25

Doing this is almost nonsense,　but if your really want, try this way:

SELECT 
    @i:=@i+1 as i,
    COUNT(*),
    SUM(a.amount) AS amount,
    FROM_UNIXTIME(a.added, '%W (%e/%m)') AS daily
FROM affiliates_earnings a, (SELECT @i:=0) b
WHERE a.added >= '1319074836'
    AND a.added <= '1319679636'
GROUP BY a.daily

MySQL $i++ equivalent

2 Answers2

Linked