CODEWITHSUNDEEP
sql-serverselectsumaverage
Macb3th
Macb3th

Reputation: 101

SQL select sum/average all rows

i have this code who picks rows from table, but i need to make him return sum of all rows or avg of all rows. I tried to put code in other select like select within sum(select), but its not working, any ideas how to make it work? Sorry for not much info. Right now it prints 25 rows. I need to make it return 1 Row that would be like sum(ontime) and avg(actual). I mean sum all 25 rows of ontime column and avg actual column.

 SELECT    AVG(CASE WHEN a.ontime = 'on time' THEN 100 ELSE 0 END)*COUNT(a.country) AS ontime
    ,AVG(CASE WHEN a.accuracy = 'accurate' THEN 100 ELSE 0 END) AS actual
    ,COUNT(a.country) AS task_count

FROM 
    bstplanning.dbo.sc_data a
    INNER JOIN bstplanning.dbo.users 
    ON a.user_id = bstplanning.dbo.users.user_id    

WHERE
    a.date >= '2015-04-01 00:00:00.000' AND a.date <= '2015-04-30 00:00:00.000' AND a.actual > 0 AND
    ((a.ontime='on time' OR a.ontime = 'late') OR (a.accuracy='accurate' OR a.accuracy = 'error'))
     and bstplanning.dbo.users.split='Rep'
     and bstplanning.dbo.users.user_surname='user'
GROUP BY
     a.country
    ,a.client
    ,a.task_group
    ,frequency
    ,a.track

Upvotes: 1

Views: 2011

Answers (1)

Arun Gairola
Arun Gairola

Reputation: 884

you can write like outer query and your query as inner query ..

SELECT Sum(Ontime) AS Ontime
    ,AVG(actual) AS Actual
    ,Sum(task_count) AS task_count
FROM (
    SELECT AVG(CASE 
                WHEN a.ontime = 'on time'
                    THEN 100
                ELSE 0
                END) * COUNT(a.country) AS ontime
        ,AVG(CASE 
                WHEN a.accuracy = 'accurate'
                    THEN 100
                ELSE 0
                END) AS actual
        ,COUNT(a.country) AS task_count
    FROM bstplanning.dbo.sc_data a
    INNER JOIN bstplanning.dbo.users ON a.user_id = bstplanning.dbo.users.user_id
    WHERE a.DATE >= '2015-04-01 00:00:00.000'
        AND a.DATE <= '2015-04-30 00:00:00.000'
        AND a.actual > 0
        AND (
            (
                a.ontime = 'on time'
                OR a.ontime = 'late'
                )
            OR (
                a.accuracy = 'accurate'
                OR a.accuracy = 'error'
                )
            )
        AND bstplanning.dbo.users.split = 'Rep'
        AND bstplanning.dbo.users.user_surname = 'user'
    GROUP BY a.country
        ,a.client
        ,a.task_group
        ,frequency
        ,a.track
    ) Drive

Upvotes: 1

Related Questions