-1

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 counts 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 how can I achieve ?

Mostafiz
  • 7,243
  • 3
  • 28
  • 42
Matthew
  • 79
  • 2
  • 8
  • why negative vote?? let me know if I need to add something. – Matthew May 04 '16 at 15:10
  • Do you have an ID field? or something to different between each one? – sagi May 04 '16 at 15:11
  • I didn't give you a downvote (though I agree that if you downvote, you should explain why), but I think more background is in order. What is the purpose of this solution? Do you really want a count of each one or some sort of ranking, as you demonstrate in your example output? What have you tried already? – Michael L. May 04 '16 at 15:12
  • what you are looking for is [`COUNT()`](http://www.w3schools.com/sql/sql_func_count.asp) – Florin Secal May 04 '16 at 15:13
  • We don't have ID field and I need to add that one. – Matthew May 04 '16 at 15:15
  • I'm not sure why you got the down votes as this is a legitimate question. What you are looking for is the SQL Server equivalent of `ROW_NUMBER() OVER (Partition By SomeCol Order By SomeCol)`. – SQLChao May 04 '16 at 15:17
  • Could you please be more specific? How can I use row_number here? I am pretty new to sql. – Matthew May 04 '16 at 15:20
  • I think you mean the MySQL equivalent of the SQL Server window functions. – Michael L. May 04 '16 at 15:20
  • 2
    @Matthew you say that each item will have it's own count but then you have separate counts for the same items in your example. Why is "IT" and "Sale" in the output multiple times with different counts? – msheikh25 May 04 '16 at 15:21
  • Unfortunately, it doesn't look like MySQL supports that type of query, but you can use that syntax in SQL Server to create a calculated ranking column in a query or view based on the "Partittion By" column (breaking the results into groups) and the "Order By" column (which defines the rank within each group). – Michael L. May 04 '16 at 15:22
  • @mo2 I mean own count is start from 1 for each item. – Matthew May 04 '16 at 15:36
  • Possible duplicate of [Sequentially number rows by keyed group in SQL?](http://stackoverflow.com/questions/5463384/sequentially-number-rows-by-keyed-group-in-sql) – Tab Alleman May 04 '16 at 18:39
  • correct grammatical format is efficient for viewers understanding – Mostafiz May 06 '16 at 16:09

1 Answers1

0

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;
SQLChao
  • 7,709
  • 1
  • 17
  • 32