Matthew Matthew - 7 months ago 10
SQL Question

Add column that counts each item

I have one column table that has some items in it say department name such as IT, IT, Sale, IT, Market, Sale, Market etc.

How can I add another column that count each department name in the following way. Basically, each item will have its own count.

For example

name added column
IT, 1
IT, 2
Sale, 1
IT, 3
Market, 1
Sale, 2


Any idea?

Answer

EDIT: Per your request I added answer for SQL Server.

SQL Server:

SELECT 
  name,
  ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) As AddedColumn
FROM YourTable

MYSQL:

SQL Fiddle Demo

SELECT 
    @row_number:=CASE
        WHEN @name = name THEN @row_number + 1
        ELSE 1
    END AS num,
    @name:=name as name
FROM
    YourTable
ORDER BY name;
Comments