CODEWITHSUNDEEP
sql-serversql-server-2012
its30
its30

Reputation: 273

SQL Server Select and Group by datediff

I am trying to group by a product and return the average time they are being used based on a datediff function.

Here is my query:

SELECT products.productNameCommon, datediff(minute,history.timeOut, history.timeIn) as session
FROM FlexLM_history history
JOIN FlexLM_products products ON products.productID=history.productID
where products.productType = 'base' and history.timeIn is Not NULL
GROUP BY products.productNameCommon, datediff(minute,history.timeOut, history.timeIn)
ORDER BY products.productNameCommon

My results are like this: 

productNameCommon | avgSession
----------------- | ----------
Product 1         | 5
Product 1         | 15
Product 1         | 40
Product 2         | 2
Product 2         | 5
Product 3         | 12
Product 3         | 24

What I really need is this:

productNameCommon | avgSession
----------------- | ----------
Product 1         | 20
Product 2         | 3.5
Product 3         | 18

Upvotes: 0

Views: 635

Answers (2)

LONG
LONG

Reputation: 4610

SELECT ABC.productNameCommon, AVG(Session) as avgSession
FROM
(
    SELECT products.productNameCommon, datediff(minute,history.timeOut, history.timeIn) as Session
    FROM FlexLM_history history
    JOIN FlexLM_products products ON products.productID=history.productID
    where products.productType = 'base' and history.timeIn is Not NULL
    GROUP BY products.productNameCommon, datediff(minute,history.timeOut, history.timeIn)
) as ABC
    GROUP BY ABC.productNameCommon  --miss this line before
    ORDER BY ABC.productNameCommon

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can use sub query and average

SELECT Q.ProductNameCommon, AvgSession = AVG(Session) FROM
( --Your query
    SELECT products.productNameCommon, datediff(minute,history.timeOut, history.timeIn) as Session
    FROM FlexLM_history history
    JOIN FlexLM_products products ON products.productID=history.productID
    where products.productType = 'base' and history.timeIn is Not NULL
    GROUP BY products.productNameCommon, datediff(minute,history.timeOut, history.timeIn)
) as Q
group by Q.ProductNameCommon
order by Q.ProductNameCommon

Upvotes: 1

Related Questions