sql type subtype 统计

select * from testtable;

 

type subtype value

a sa 1
b sb 1
a sb error
b sa error
a sb 1
b sb 1
c sa error
c sb 1
c sc 1
c sc 1
c sa error

select sum(`value`) from testtable where value != 'error' AND type ='b';

sum(`value`) 

2

SELECT type,subtype,SUM(value) AS TOTNUM FROM testtable WHERE value !='error' GROUP BY type,subtype;

type subtype TOTNUM

a sa 1
a sb 1
b sb 2
c sb 1
c sc 2
c sc 2

SELECT type,subtype,COUNT(value) AS TOTCOUNT FROM testtable WHERE value ='error' GROUP BY type,subtype;

type subtype TOTCOUNT

a sb 1
b sa 1
c sa 2
c sa 2

select * from (select type,subtype,sum(case when value <> 'error' then value else '0' end) as 总数 ,sum(case when value = 'error' then 1 else '0' end) as error行数 from testtable group by type,subtype) t ORDER BY 总数 desc

 

type subtype 总数 error行数

 

c sc 2 0
b sb 2 0
a sa 1 0
c sb 1 0
a sb 1 1
c sa 0 2
b sa 0 1