如何根据不同的值获得计数总和 [英] How to get sum of count based on distinct values

问题描述

select distinct(UserName), case when (column1 <> column2
        OR (column1 IS NULL AND column2 IS NOT NULL)
        OR (column1 IS NOT NULL AND column2 IS NULL)) then
     Count(column3)  else '0' end as Count
 from table1 where column4='test'  Group BY column1 , column2, UserName

用户名数

约翰0

mary 0

mary 1

mary 20

sean 0

sam 0



i我得到的结果如上，但我需要基于计数的总和关于用户名的唯一性

for ex：Mary在表中重复了3次，但我需要结果为mary：21在sql查询中。

请帮助我这个



提前感谢

UserName Count
john 0
mary 0
mary 1
mary 20
sean 0
sam 0

i am getting results like above but i need sum of count based on unique of username
for ex: Mary is repeated 3 time in the table but i need the result as mary:21 in sql query.
Please help me on this

thanks in advance

推荐答案

你正在使用旗帜说明如果第4列和第5列是对于给定的行不同。然后你只需要按用户名对这些标志求和。

You are on good way with the flag which says if column4 and column5 are different for the given row. Then you just need to sum those flags by user name.

select 
  column3 as UserName, 
  Sum(case 
    when (column4 <> column5
      OR (column4 IS NULL AND column5 IS NOT NULL)
      OR (column4 IS NOT NULL AND column5 IS NULL)) then 1 
    else 0 
  end) as Count
from table1 where column1='test' 
group by column3

我不确定，但基于我的观察，我想你想要为每个用户显示数据。

试试这样，

I'm not sure, but based on my Observation, i think you want show data for each users.
Try like this,

select UserName,sum(Count) as Count from table1 group by UserName

我认为这也应该有效，请检查。

i think this should also work,check it.

select 
     Column3 as UserName,Count(1) as Count 
from 
    table1 
Where 
      isnull(column4,'')<>isnull(column5,'')
group by Column3

这篇关于如何根据不同的值获得计数总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！