Eric S Eric S - 3 months ago 13
SQL Question

SQL Show COUNT with AVG

I am trying to combine a

COUNT
that contains a
GROUP BY
with a
AVG
statement. I am using MSSql.

Is it possible to have the
AVG
next to the
COUNT
results?

COUNT SQL:

SELECT ActionId, COUNT(ActionId) as ActionCount
FROM AIFMESSAGELOG
WHERE DIRECTION = 1 AND CREATEDDATETIME >= '2016-08-15 23:59:59.000' AND CREATEDDATETIME <= '2016-08-16 23:59:59.000'
Group By ActionId
Order By ActionCount DESC


This returns:

ActionId ActionCount
Insert Action 672
Update Action 184


AVG SQL:

SELECT DATEDIFF(millisecond, b.CREATEDDATETIME, a.CREATEDDATETIME)*1.0 AS AvgTime
FROM AIFMESSAGELOG a
INNER JOIN AIFMESSAGELOG b
ON a.REQUESTMESSAGEID = b.MESSAGEID
WHERE b.CREATEDDATETIME >= '2016-08-15 23:59:59.000' AND b.CREATEDDATETIME <= '2016-08-16 23:59:59.000' AND a.ACTIONID = 'Insert Action'
ORDER BY A.CREATEDDATETIME DESC


This returns:

AvgTime
50


Result Needed:

ActionId ActionCount AvgTime
Insert Action 672 50
Update Action 184 25


My Code:

SELECT a.ActionId, COUNT(a.ActionId) as ActionCount, b.FinalAvg
FROM AIFMESSAGELOG a
INNER JOIN
(
SELECT AVG(AvgTime) AS FinalAvg, ACTIONID
FROM
(
SELECT c.ActionID, DATEDIFF(millisecond, d.CREATEDDATETIME, c.CREATEDDATETIME)*1.0 AS AvgTime
FROM AIFMESSAGELOG c
INNER JOIN AIFMESSAGELOG d
ON c.REQUESTMESSAGEID = d.MESSAGEID
)MyTable
) b
ON a.ACTIONID = b.ACTIONID
WHERE a.DIRECTION = 1 AND a.CREATEDDATETIME >= '2016-08-15 23:59:59.000' AND a.CREATEDDATETIME <= '2016-08-16 23:59:59.000'
Group By a.ActionId
Order By ActionCount DESC


I get the following error when I try to run this last query:


Column 'MyTable.ActionID' is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.


Please do note, I have to do a
DATEDIFF
to get from 2 different rows in the same table and column.
My table:

AIFMESSAGELOG
-------------
ACTIONID
REQUESTMESSAGEID
MESSAGEID
DIRECTION
CREATEDDATETIME

Answer

Consider a join of two aggregate derived tables, joined by ACTIONID:

SELECT t1.ActionId, t1.ActionCount, t2.AvgTime

FROM    
   (SELECT ActionId, COUNT(ActionId) as ActionCount
    FROM AIFMESSAGELOG
    WHERE DIRECTION = 1 
    AND CREATEDDATETIME >= '2016-08-15 23:59:59.000' 
    AND CREATEDDATETIME <= '2016-08-16 23:59:59.000'
    GROUP By ActionId) As t1

INNER JOIN    
   (SELECT a.ACTIONID, 
           AVG(DATEDIFF(millisecond, b.CREATEDDATETIME, a.CREATEDDATETIME)*1.0) AS AvgTime
    FROM AIFMESSAGELOG a
    INNER JOIN AIFMESSAGELOG b
            ON a.REQUESTMESSAGEID = b.MESSAGEID
    WHERE b.CREATEDDATETIME >= '2016-08-15 23:59:59.000' 
    AND b.CREATEDDATETIME <= '2016-08-16 23:59:59.000'
    GROUP BY a.ACTIONID) AS t2

ON t1.ActionId = t2.ACTIONID
ORDER BY t1.ActionCount DESC