CODEWITHSUNDEEP
sql
Egidio
Egidio

Reputation: 1

Sql Query Average of a Sum

I have the following query that is taking the count from 2 subquerys and it should give me the average of both count. It is giving an error on C1 in the last query.

Select c0.hour , AVG(c0.frequency)as 'AVG In', AVG(c1.frequency)as 'AVG Out' from
(SELECT  [Network]
,cast ([date time]as date)as 'date'
,datepart(hh,[date time])as 'hour'
,[Scan Type]
,count ([scan type])as frequency    
 FROM [Pallex-DW].[dbo].[Scans]
 where Network like 'fr'and [Scan Type] like '3'
 group by Network ,datepart(hh,[date time]),cast ([date time]as date),[Scan Type])as c0

 Union  

 (SELECT  [Network]
,cast ([date time]as date)as 'date'
,datepart(hh,[date time])as 'hour'
,[Scan Type]
,count ([scan type])as frequency
 FROM [Pallex-DW].[dbo].[Scans] as c1
 where Network like 'fr'and [Scan Type] like '11'
 group by Network ,datepart(hh,[date time]),cast ([date time]as date),[Scan Type])c1

Upvotes: 0

Views: 89

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

The problem with your query is actually c0. The union does not need an alias on the first query:

Try this version:

Select
c0.hour,AVG(c0.frequency)as 'AVG In',AVG(c1.frequency)as 'AVG Out'
from
((SELECT  [Network]
    ,cast ([date time]as date)as 'date'
    ,datepart(hh,[date time])as 'hour'
    ,[Scan Type]
    ,count ([scan type])as frequency
  FROM [Pallex-DW].[dbo].[Scans]
  where Network like 'fr'and [Scan Type] like '3'
  group by Network ,datepart(hh,[date time]),cast ([date time]as date),[Scan Type])
  Union  
(SELECT  [Network]
    ,cast ([date time]as date)as 'date'
    ,datepart(hh,[date time])as 'hour'
    ,[Scan Type]
    ,count ([scan type])as frequency
  FROM [Pallex-DW].[dbo].[Scans] as c1
  where Network like 'fr'and [Scan Type] like '11'
  group by Network ,datepart(hh,[date time]),cast ([date time]as date),[Scan Type])) as c

But, I think you can greatly simplify the query by removing the union and selecting all the rows in one subquery:

Select c.hour, AVG(case when [scan type] = '3' then 1.0*c.frequency end) as "AVG In",
       AVG(case when [scan type] = '3' then 1.0*c.frequency end) as "AVG Out"
from ((SELECT  [Network], 
               cast([date time]as date)as "date", 
               datepart(hh,[date time])as "hour",
               [Scan Type],
               count ([scan type])as frequency
       FROM [Pallex-DW].[dbo].[Scans]
       where Network like 'fr'and [Scan Type] in ( '3', '11')
       group by Network ,datepart(hh,[date time]),cast ([date time]as date),[Scan Type]
      ) as c
group by hour

You also need a group by clause for the final result. I also multiplied the values by 1.0 to convert them to a non-integer value. Averages of integers are integers, which can be misleading. I also removed the single quotes around the column names; use double quotes or square brackets (to avoid confusion with actual constants).

Upvotes: 1

Lokesh
Lokesh

Reputation: 7940

Why are you using union?

Below query should work, i have removed union. You need place somthing in where clause to join c1 and c2.

Select
c0.hour,AVG(c0.frequency)as 'AVG In',AVG(c1.frequency)as 'AVG Out'
from
(SELECT  [Network]
    ,cast ([date time]as date)as 'date'
    ,datepart(hh,[date time])as 'hour'
    ,[Scan Type]
    ,count ([scan type])as frequency
  FROM [Pallex-DW].[dbo].[Scans]
  where Network like 'fr'and [Scan Type] like '3'
  group by Network ,datepart(hh,[date time]),cast ([date time]as date),[Scan Type])as c0, 
(SELECT  [Network]
    ,cast ([date time]as date)as 'date'
    ,datepart(hh,[date time])as 'hour'
    ,[Scan Type]
    ,count ([scan type])as frequency
  FROM [Pallex-DW].[dbo].[Scans] as c1
  where Network like 'fr'and [Scan Type] like '11'
  group by Network ,datepart(hh,[date time]),cast ([date time]as date),[Scan Type])c1

Upvotes: 0

Related Questions