CODEWITHSUNDEEP
sql-server
Kumee
Kumee

Reputation: 211

Display count of group by function in same column

I am trying display the date along with count in the same column in SQL query

select top 5  COUNT(IssueDate), IssueDate 
from employee (Nolock) 
group by CardIssueDate  ORDER BY COUNT(IssueDate) DESC

I need result as

IssueDate:

2015-09-11 (23)
2015-09-29 (89)
2015-08-20 (78)
2016-06-08 (2)
2015-10-29 (234)

Any guidance on this how to get it working like above result?

Thanks

Upvotes: 0

Views: 46

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93734

use + or Concat function to concatenate the result

For older versions

SELECT TOP 5 CONVERT(VARCHAR(10), IssueDate, 20) + ' ('+ Cast(Count(IssueDate) AS VARCHAR(50))+ ')'
FROM   employee (Nolock)
GROUP  BY CardIssueDate
ORDER  BY Count(IssueDate) DESC

For new versions(2012+)

SELECT TOP 5 Concat(CONVERT(VARCHAR(10), IssueDate, 20), ' (', Count(IssueDate), ')')
FROM   employee (Nolock)
GROUP  BY CardIssueDate
ORDER  BY Count(IssueDate) DESC

Upvotes: 2

Related Questions