MS SQL count AS to new column

Question

Good day

I have problem with my table and counting

TABLE1
COLUMN1       COLUMN2
3              jjd
5              jd
3              jjd
4              kg 
5              jd
48             gjh
446            djj
…               …

I need

TABLE1 
COLUMN1 COLUMN2 COLUMN3
3         jj         2
5         jd         2
4         kg         1
48        gjh        1
446       djj        1
...        ...       …

Iam doing but not working well.

SELECT * , COUNT(Column1) as column3 FROM TABLE1

Thanks for help withh my counting

Suggestion: first read the error messages received, second post those message when asking for help. The error message pretty much tells you how to resolve this. — Sean Lange, Jul 31 '17 at 14:24
Possible duplicate of [How to use count and group by at the same select statement](https://stackoverflow.com/questions/2722408/how-to-use-count-and-group-by-at-the-same-select-statement) — S3S, Jul 31 '17 at 14:27

score 0 · Answer 1 · 2017-07-31T14:29:13.360

Try by using group by

    SELECT COLUMN1 , 
       COLUMN2 ,
       COUNT(Column1) As COLUMN3 FROM cte_TABLE1
    Group by COLUMN1 ,COLUMN2 
    Order by COLUMN1

By using Window function

SELECT DISTINCT COLUMN1 , 
       COLUMN2 ,
       COUNT(Column1)OVER(Partition by COLUMN1,COLUMN2 ORder by COLUMN1 ) As COLUMN3 FROM cte_TABLE1

Result

COLUMN1 COLUMN2 column3
-----------------------
3       jjd      2
4       kg       1
5       jd       2
48      gjh      1
446     djj      1

score 0 · Answer 2 · answered Jul 31 '17 at 14:28

Use a GROUP BY and ORDER BY with DESC to put them in COUNT total order.

SELECT COLUMN1, COLUMN2, COUNT(Column1) AS COLUMN3 
FROM Table1
GROUP BY COLUMN1, COLUMN2
ORDER BY COUNT(Column1) DESC

Output

COLUMN1 COLUMN2 COLUMN3
5       jd      2
3       jjd     2
4       kg      1
446     djj     1
48      gjh     1

SQL Fiddle: http://sqlfiddle.com/#!6/89f49/4/0

score 0 · Answer 3 · answered Jul 31 '17 at 14:31

0

Using OVER we can achieve it easily

SELECT  COLUMN1 , 
       COLUMN2 ,
       COUNT(Column1)OVER(Partition by COLUMN1,COLUMN2 ORder by COLUMN1 ) As COLUMN3 FROM cte_TABLE1
Group By COLUMN1,COLUMN2

answered Jul 31 '17 at 14:31

Ashu

462
3
16

MS SQL count AS to new column

3 Answers3