CODEWITHSUNDEEP
mysql
Davi Amaral
Davi Amaral

Reputation: 518

RowNum in Mysql, with group by (MySQL 5.x)

I would like to do a rownum in MySql but using the group by. So the rownum need to start again when de column city change.

I tried that: 

> select city, category_name, sales,    @row_number:=CASE WHEN
> @db_names=sub_table.city THEN @row_number+1 ELSE 1 END AS row_number
> from ( SELECT city, category_name, sum(subtotal) as sales from
> customers     join orders on customers.customer_id = orders.customer_id
>     join order_items on orders.order_id = order_items.order_id
>     join products on order_items.product_id = products.product_id
>     join categories on products.category_id = categories.category_id
>     group by city, category_name
>     ) sub_table ORDER BY sub_table.city ASC, sub_table.sales DESC;

But I got this result:

enter image description here

Upvotes: 0

Views: 134

Answers (2)

nbk
nbk

Reputation: 49373

i programmed it like other query i made here on SO.

You should also use aliases for your tables.

 SELECT 
    `city`,
    `category_name`,
    `sales`
     ,IF(@db_names = city , @row_number:= @row_number + 1  , @row_number:= 1 ) AS row1_number
     ,@db_names := city
FROM
    (SELECT 
        city, category_name, SUM(subtotal) AS sales
    FROM
        customers
    JOIN orders ON customers.customer_id = orders.customer_id
    JOIN order_items ON orders.order_id = order_items.order_id
    JOIN products ON order_items.product_id = products.product_id
    JOIN categories ON products.category_id = categories.category_id
    GROUP BY city , category_name
    ORDER BY city , category_name) sub_table
    ,(SELECT @dab_names := '') c1,(SELECT @row_number := 0) d1
ORDER BY sub_table.city ASC , sub_table.sales DESC;

Without a sampe to test it properly it is

Upvotes: 1

PeterHe
PeterHe

Reputation: 2766

You are very close, just the variable @db_name not assigned value:

select city, category_name, sales,    
  @row_number:=CASE WHEN
    @db_names=sub_table.city THEN @row_number+1 ELSE 1 END AS row_number,
  @db_name:=sub_table.city
from ( SELECT city, category_name, sum(subtotal) as sales from
customers     join orders on customers.customer_id = orders.customer_id
    join order_items on orders.order_id = order_items.order_id
    join products on order_items.product_id = products.product_id
    join categories on products.category_id = categories.category_id
    group by city, category_name
) sub_table ORDER BY sub_table.city ASC, sub_table.sales DESC;

Upvotes: 1

Related Questions