SELECT在COUNT个条件最小的列组合中出现 [英] SELECT occurences of a combination of columns with a minimum condition on the COUNT

问题描述

我每天都会在表OptionsData中存储一些数据。在此表中，我对两列 asofdate感兴趣。和合同。 asofdate + contract的组合应该是唯一的：如果不是，那么我需要进行一些清理。我想返回3列如下：
asofdate！合同！计数> 1

这将使我能够识别表中的重复项。我尝试了以下操作：

 选择asofdate，合同，count（*）mycount 
 from（选择asofdate，合同
  OptionsData 
按截止日期分组，合同
）作为派生表
 GROUP BY截止日期，合同
具有mycount> 1 
由mycount命令排序
  

但这会返回错误：

 错误：列 mycount不存在
  

如果我指定了

  HAVING DerivedTable.mycount> 1 
  

（我也尝试了WHERE语句而不是HAVING语句，但这又产生了另一个错误：

 错误： WHERE 
  

或附近的语法错误/ p>

不用说我是sql的初学者...

解决方案

GROUP BY 子句中的别名。此外：为什么使用子查询？它将每个asofdate和合约的行数减少到一，因此，如果您随后计算 ，则每个asofdate /合约对的计数都为1。 从public.asofdate中选择asofdate，合同，count（*）作为mycount
。按asofdate，optionsdata
分组，count（*）> mycount desc发出的1张
订单；


I am storing some data everyday in a table OptionsData. In this table I am interested in two columns "asofdate" and "contract". The combination of asofdate+contract should be unique: if not then I need to do some clean up. I would like to return 3 columns as follows: asofdate !! contract !! count > 1

This will allow me to identify duplicates in my table. I have tried the following:

select asofdate, contract, count(*) mycount 
from (select asofdate, contract
      from public."OptionsData"
      group by asofdate, contract
      ) AS DerivedTable
GROUP BY asofdate, contract
HAVING mycount > 1
ORDER BY mycount DESC

But this returns an error:

ERROR:  column "mycount" does not exist

Same thing happens if I specify

HAVING DerivedTable.mycount > 1

(I also tried a WHERE statement instead of HAVING but this gives another error:

ERROR:  syntax error at or near "WHERE"

)

Needless to say I am a beginner in sql...

解决方案

You cannot use an alias name in the GROUP BY clause. Besides: Why the subquery? It reduces the rows to one per asofdate and contract, so if you count afterwards you get a count of 1 for each asofdate / contract pair.

select asofdate, contract, count(*) as mycount 
from public.optionsdata
group by asofdate, contract
having count(*) > 1
order by mycount desc;

这篇关于SELECT在COUNT个条件最小的列组合中出现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！