如何计算特定项在查询输出中出现的次数

Question

我有一个包含很多表和连接的查询。因此，简单的计数(列)和分组依据不能正常工作。该查询返回一个项目列表。我想要计算每一项在该列表中出现的次数。

下面是我的问题：

SELECT DECODE(BOM.ORGANIZATION_ID,203, 'CEC', 328, '3PL', 204, 'SIM') ORGANIZATION_CODE,
  BOM.ORGANIZATION_ID,
  MSI.SEGMENT1 "PARENT_ITEM",
  MSIC.SEGMENT1 "COMPONENT_ITEM",
  SUM(NVL(MMT.TRANSACTION_QUANTITY,0)) * NVL(SUM(CIC.ITEM_COST),0) "ANNUAL_MONEY",
  SUM(NVL(MMT.TRANSACTION_QUANTITY,0)) "ANNUAL_QTY_USG",
  NVL(SUM(CIC.ITEM_COST),0) "AVG_COST",
  SUM(NVL(MSI.POSTPROCESSING_LEAD_TIME,0) + NVL(MSI.PREPROCESSING_LEAD_TIME,0)) LEAD_TIME
FROM BOM_BILL_OF_MATERIALS BOM
LEFT JOIN MTL_SYSTEM_ITEMS MSI
ON MSI.ORGANIZATION_ID    = BOM.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
AND MSI.ENABLED_FLAG      = 'Y'
AND MSI.BOM_ENABLED_FLAG  = 'Y'
LEFT JOIN CST_ITEM_COSTS CIC
ON CIC.ORGANIZATION_ID    = BOM.ORGANIZATION_ID
AND CIC.INVENTORY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
AND CIC.COST_TYPE_ID      = 1
LEFT JOIN MTL_MATERIAL_TRANSACTIONS MMT
ON MMT.INVENTORY_ITEM_ID     = BOM.ASSEMBLY_ITEM_ID
AND MMT.ORGANIZATION_ID      = BOM.ORGANIZATION_ID
AND MMT.TRANSACTION_TYPE_ID IN (33,34,17)
LEFT JOIN MTL_TRANSACTION_TYPES MTT
ON MTT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
LEFT JOIN BOM_INVENTORY_COMPONENTS BIC
ON BIC.BILL_SEQUENCE_ID = BOM.COMMON_BILL_SEQUENCE_ID
AND NVL(bic.disable_date, sysdate+1) > sysdate
LEFT JOIN BOM_COMPONENTS_B BCB
ON BIC.COMPONENT_SEQUENCE_ID = BCB.COMPONENT_SEQUENCE_ID
LEFT JOIN BOM_STRUCTURES_B BSB
ON BCB.BILL_SEQUENCE_ID = BSB.BILL_SEQUENCE_ID
LEFT JOIN MFG_LOOKUPS ML
ON ML.LOOKUP_CODE  = BIC.WIP_SUPPLY_TYPE
AND ML.LOOKUP_CODE = BIC.ENFORCE_INT_REQUIREMENTS
AND ML.LOOKUP_TYPE = 'MTL_EAM_ITEM_TYPE'
LEFT JOIN MTL_SYSTEM_ITEMS MSIC
ON MSIC.ORGANIZATION_ID    = BOM.ORGANIZATION_ID
AND MSIC.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID
LEFT JOIN BOM_OPERATIONAL_ROUTINGS BOR
ON BOR.ASSEMBLY_ITEM_ID   = BOM.ASSEMBLY_ITEM_ID
AND BOR.ORGANIZATION_ID   = BOM.ORGANIZATION_ID
WHERE BOM.ORGANIZATION_ID = NVL(:P_ORG_ID,BOM.ORGANIZATION_ID)
AND EXISTS
  (SELECT 'X'
  FROM MTL_MATERIAL_TRANSACTIONS MMT
  WHERE MMT.TRANSACTION_DATE >= ADD_MONTHS (SYSDATE,-12)
  AND MSI.INVENTORY_ITEM_ID IS NOT NULL
  )
GROUP BY BOM.ORGANIZATION_ID,
  MSI.SEGMENT1,
  MSIC.SEGMENT1
ORDER BY ORGANIZATION_CODE,
  MSIC.SEGMENT1

我现在的输出是这样的：

​

我想要有另一个列来计算输出中出现的每个项目。

Answer 1

这就是所谓的条件聚合，你可以用CASE EXPRESSION来实现：

SELECT <col1>,<col2>..,
       COUNT(CASE WHEN column ='Specific Val' THEN 1 END) as cnt
FROM YourTable
GROUP BY  <col1>,<col2>..,

在MySQL中，这也很好：

SUM(column = 'Specific Val')

因为表达式被视为1和0。

Answer 2

count(distinct列)在这里不起作用吗?因为您已经在使用group by

Answer 3

您还可以尝试执行以下操作：

select count(*) from
(your query)
where column='value to be checked'