1

I have a table with date and kind of issue. It looks like:

Date                    | Issue
2018-01-01 09:32:33.000 | Problem 1
2018-01-01 19:47:11.000 | Problem 1
2018-01-01 14:25:16.000 | Problem 2
2018-01-02 11:07:20.000 | Problem 1
2018-01-02 19:18:51.000 | Problem 2
2018-01-03 20:45:41.000 | Problem 1
2018-01-03 04:27:56.000 | Problem 1
2018-01-03 15:27:56.000 | Problem 2
2018-01-03 09:27:56.000 | Problem 2
2018-01-03 22:27:56.000 | Problem 3

I want to count the total amount of each problem for each day. I want it to look like:

Date        | Issue       | Amount
2018-01-01  | Problem 1   | 2
2018-01-01  | Problem 2   | 1
2018-01-02  | Problem 1   | 1
2018-01-02  | Problem 2   | 1
2018-01-03  | Problem 1   | 2
2018-01-03  | Problem 2   | 2
2018-01-03  | Problem 3   | 1

Now I have this, but obviously it doesn't work:

SELECT CONVERT(VARCHAR, CONVERT(DATE TIME, Date), 23) AS Date,
       Issue,
       COUNT(Date) AS Amount
FROM Issues
GROUP BY Date, Issue
ORDER BY Date, Issue

Thanks in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Daniil Pastukhov
  • 68
  • 8

1 Answers1

2

You're close:

SELECT CONVERT(date,[Date]) AS [Date], --I suggest a better name for your column, as that's confusing
       Issue,
       COUNT(Date) AS Amount
FROM Issues
GROUP BY CONVERT(date,[Date]), Issue
ORDER BY [Date], Issue; --[Date] here will reference the alias, rather than the column

You need the expression in the GROUP BY as well.

Thom A
  • 88,727
  • 11
  • 45
  • 75