How to count events based on parsing date - GROUP BY issues

Question

I am attempting to count how many FAILURE events occurred per day, and the events are stored in a MainEventTable with columns of EventDateTime, EventId, and EventStatus. I'm using SQL Server Management Studio 2016, and it didn't recognize the DATEFROMPARTS function. This is the code that I've put together so far:

SELECT 
    t.EventDate, 
    SUM(t.EventCount) AS EventCount 
FROM (
    SELECT 
        CAST(
                (
                    CAST(
                        DATEPART(yyyy,s.EventDateTime) 
                        AS VARCHAR(4)
                        ) + '-' + 
                    CAST(
                        DATEPART(mm,s.EventDateTime) 
                        AS VARCHAR(2)
                        ) + '-' + 
                    CAST(
                        DATEPART(dd,s.EventDateTime) 
                        AS VARCHAR(2)
                        )
                ) AS DATE
            ) AS EventDate,
        Count(s.EventId) AS EventCount 
    FROM (
        SELECT 
            EventDateTime, 
            EventId 
        FROM 
            MainEventTable WITH(NOLOCK) 
        WHERE EventDateTime > '2016-12-07 00:00:00' 
        AND EventStatus = 'FAILURE'
        ) AS s GROUP BY CAST(
                (
                    CAST(
                        DATEPART(yyyy,s.EventDateTime) 
                        AS VARCHAR(4)
                        ) + '-' + 
                    CAST(
                        DATEPART(mm,s.EventDateTime) 
                        AS VARCHAR(2)
                        ) +  '-' + 
                    CAST(
                        DATEPART(dd,s.EventDateTime) 
                        AS VARCHAR(2)
                        )
                ) AS VARCHAR(10)
            )
    ) AS t
GROUP BY t.EventDate;

UPDATE: (THANKS to @wrslphil and @PM_77-1 for assistance with my GROUP BY issues) I've fixed my GROUP BY issues above and found that this worked, although it was very clunky. @KeithL simplified it MUCH more below...

Please read [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/questions/284236/why-is-can-someone-help-me-not-an-actual-question) before attempting to ask more questions. — , Jul 21 '17 at 15:34
[How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/questions/261592/how-much-research-effort-is-expected-of-stack-overflow-users) The answer, *"A lot. An absurd amount. More than you think you are capable of. After you have reached the end of your rope and the pain of not having the answer exceeds the vast amount of shame received by posting your question, that's when you can go ahead and ask. Because at that point, you will have done whatever research necessary to make it a good question worth asking."* — , Jul 21 '17 at 15:34
Possible duplicate of [Column "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"](https://stackoverflow.com/questions/18258704/column-invalid-in-the-select-list-because-it-is-not-contained-in-either-an-aggr) — , Jul 21 '17 at 15:34

score 2 · Answer 1 · answered Jul 21 '17 at 15:47

Not a lot to go on here, but the query definitely won't run if you have an item that isn't in the group by list or aggregated in your select clause

I would think that you probably need to do a sum of your count field... like so

SELECT
    t.EventDate,
    SUM(t.EventCount) as EventCount
FROM (
    SELECT
        CAST(
                (
                    CAST(
                        DATEPART(yyyy,s.EventDateTime)
                        AS VARCHAR(4)
                        ) +
                    CAST(
                        DATEPART(mm,s.EventDateTime)
                        AS VARCHAR(2)
                        ) +
                    CAST(
                        DATEPART(dd,s.EventDateTime)
                        AS VARCHAR(2)
                        )
                ) AS DATE
            ) AS EventDate,
        Count(s.EventId) As EventCount
    FROM (
        SELECT
            EventDateTime,
            EventId
        FROM
            MainEventTable WITH(NOLOCK)
        WHERE EventDateTime > '2016-12-07 00:00:00'
        AND EventStatus = 'FAILURE'
        ) AS s
    ) AS t
GROUP BY t.EventDate;

The query is still unlikely to run because you have the same issue in your inner query. I would probably just do the transformation of the date within an inner query and encapsulate it with a query containing a count and group by

PM 77-1 · Answer 2 · 2017-07-21T23:48:24.117

Your sub-query has the following structure:

SELECT field1, COUNT(field2) 
FROM theTable

In the same select you use a "raw" field and aggregation (COUNT). You do not have GROUP BY clause.

You are confusing your SQL engine, since it's impossible to figure out what value of field1 should be picked.

If you intention was to count how many records with non-NULL value of field2 each value of field1 has then the code would be:

SELECT field1, COUNT(field2) 
FROM theTable
GROUP BY field1

score 1 · Accepted Answer · answered Jul 21 '17 at 15:50

1

select CAST(EventDateTime AS DATE),COUNT(*)
FROM MainEventTable
WHERE EventDateTime > '2016-12-07 00:00:00' 
        AND EventStatus = 'FAILURE'
GROUP BY CAST(EventDateTime AS DATE)

answered Jul 21 '17 at 15:50

KeithL

5,348
3
19
25

So, if I change the first line to `select CAST(EventDateTime AS DATE)as EventDate, COUNT(*) as EventCount`, why can't I `GROUP BY EventDate`? It says `Invalid column name 'EventDate'.` – LHM Aug 04 '17 at 17:01
1

@LHM Only order by allows aliases (or location i.e. order by 1 (column 1)) – KeithL Aug 07 '17 at 12:46

How to count events based on parsing date - GROUP BY issues

3 Answers3