Question

我要问的是,什么是罚款?

select case month(timestamp_iso(STATUSDATE))
        when 1 then  January 
        when 2 then  February 
        when 3 then  March 
        when 4 then  April 
        when 5 then  May 
        when 6 then  Jun 
        when 7 then  July 
        when 8 then  August 
        when 9 then  September  
        when 10 then  October 
        when 11 then  November 
        when 12 then  December 
    end as Month, 

    count (case when service= ADSL  then 1 end) as  ADSL,
  AVG (timestampdiff(
  4, 
  char(actualfinish - reportdate))/60.00) as efficiecny



from INCIDENT
where   year(STATUSDATE) = year(current_timestamp) 
group by month(timestamp_iso(STATUSDATE))

I want to get for each month number of services with ADSL (it is done through the first COUNT) and average time difference in hours for the records which do not have service ADSL. So I must exclude in the AVG function all records with service ADSL but I can not put it in where clause

 where   year(STATUSDATE) = year(current_timestamp) and service!= ADSL

由于我的计算机辅助设备职能需要服务 ADSL

如何解决这一问题?

成就

Answer 1

Avg()总功能忽视了,如果服务是ADSL ,则你可以宣布无效。

   AVG (case when service !=  ADSL 
             then timestampdiff(4, char(actualfinish - reportdate))/60.00) 
             else null
         end) as efficiency

您可尝试rel=“nofollow”>coalesce :

   COALESCE (AVG (case when service !=  ADSL 
             then timestampdiff(4, char(actualfinish - reportdate))/60.00) 
             else null
         end), 0) as efficiency

Answer 2

I m sure there s some trick you can use to do this all in one query, but these should really be two separate queries, one counting ADSL, and one doing the average of non-ADSL. If you really want to do it in one round trip, you could do a pair of subqueries, then join the subqueries on the month. The whole thing will be a lot more readable, faster, easier to maintain, plus more canonical SQL.

并且,如DB2一样,将本月的名称改为:MONTHNAME:

诚然,因为你坚持...... 我不熟悉db2,因此,yn子可能很少,但你应当能够做这样的事情:

SELECT avgt.month, countt.data, avgt.data FROM

(SELECT month(timestamp_iso(STATUSDATE)) AS month,count(*) AS data FROM INCIDENT WHERE year(statusdate) = year(current_timestamp) AND service= ADSL  GROUP BY month(timestamp_iso(STATUSDATE))) AS countt

INNER JOIN 

(SELECT month(timestamp_iso(STATUSDATE)) as month,avg(...) AS data FROM INCIDENT WHERE year(statusdate) = year(current_timestamp) AND service!= ADSL  GROUP BY month(timestamp_iso(STATUSDATE))) AS avgt

ON avgt.month=countt.month

另一种做法是,在武装部队内部立案,如果是服务,则给予全国人民党以权力。 ADSL。从AVG的文献中看,这并不明显,而是与NUL的价值观相干的,但值得一击。

Answer 3

Calculate AVG salary excluding min() and max() salaries in a table.

select round(avg(table.salary),2) from table
where table.salary NOT IN ((select min(salary) from table), (select max(salary) from table))

友情链接