如何取出分组后数据的行数和???????
如何取出分组后数据的行数和???????
这条语句count(*)为何得不到select出来的总行数???
SELECT count(*),
 we.wip_entity_name,
 MSI2.SEGMENT1,
 sum(WRO.REQUIRED_QUANTITY) QTY,
 MSI.PRIMARY_UNIT_OF_MEASURE UOM,
 wro.QUANTITY_PER_ASSEMBLY
  FROM WIP.WIP_DISCRETE_JOBS WDJ,
  WIP.WIP_REQUIREMENT_OPERATIONS WRO,
  INV.MTL_SYSTEM_ITEMS MSI,
  WIP.WIP_OPERATIONS WO,
  INV.MTL_SYSTEM_ITEMS MSI2
  ,wip.wip_entities we
 WHERE WDJ.STATUS_TYPE IN (1, 3, 6)
  AND WDJ.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
  AND WRO.ORGANIZATION_ID = 4
  AND MSI.ORGANIZATION_ID = 4
  AND WRO.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID  
  AND WO.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
  AND WRO.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
  AND nvl(WRO.QUANTITY_ISSUED, 0) = 0
  and nvl(WRO.REQUIRED_QUANTITY, 0) > 0
  AND MSI2.ORGANIZATION_ID = 4
  AND WDJ.PRIMARY_ITEM_ID = MSI2.INVENTORY_ITEM_ID
  and wdj.wip_entity_id=we.wip_entity_id  
  and substr(MSI.SEGMENT1,1,1) in ('7')
  and MSI.SEGMENT1 like '7190-020160%'
  group by
  we.wip_entity_name,
  MSI2.SEGMENT1,
  MSI.PRIMARY_UNIT_OF_MEASURE ,
  wro.QUANTITY_PER_ASSEMBLY
结果如下：
count(*) wip_entity_name SEGMENT1 qty uom QUANTITY_PER_ASSEMBLY
1 FJ2012060501 3459-403180 18.6 kg 9.3
1 FJ2012060502 3459-403180 18.6 kg 9.3
明明是行数是两行，我希望能得到行数2，但count(*)却是1，
在网上看到说在group by中count取得是分组的统计行数 可以改为count(count(*))over() 就能取出分组后数据的行数和
于是我在上面语句select最前面加了count(count(*))over()，语句如下：
SELECT count(count(*))over(),
 we.wip_entity_name,
 MSI2.SEGMENT1,
 sum(WRO.REQUIRED_QUANTITY) QTY,
 MSI.PRIMARY_UNIT_OF_MEASURE UOM,
 wro.QUANTITY_PER_ASSEMBLY
  FROM WIP.WIP_DISCRETE_JOBS WDJ,
  WIP.WIP_REQUIREMENT_OPERATIONS WRO,
  INV.MTL_SYSTEM_ITEMS MSI,
  WIP.WIP_OPERATIONS WO,
  INV.MTL_SYSTEM_ITEMS MSI2
  ,wip.wip_entities we
 WHERE WDJ.STATUS_TYPE IN (1, 3, 6)
  AND WDJ.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
  AND WRO.ORGANIZATION_ID = 4
  AND MSI.ORGANIZATION_ID = 4
  AND WRO.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID  
  AND WO.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
  AND WRO.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
  AND nvl(WRO.QUANTITY_ISSUED, 0) = 0
  and nvl(WRO.REQUIRED_QUANTITY, 0) > 0
  AND MSI2.ORGANIZATION_ID = 4
  AND WDJ.PRIMARY_ITEM_ID = MSI2.INVENTORY_ITEM_ID
  and wdj.wip_entity_id=we.wip_entity_id  
  and substr(MSI.SEGMENT1,1,1) in ('7')
  and MSI.SEGMENT1 like '7190-020160%'
  group by
  we.wip_entity_name,
  MSI2.SEGMENT1,
  MSI.PRIMARY_UNIT_OF_MEASURE ,
  wro.QUANTITY_PER_ASSEMBLY
pl sql developer报错:
ORA-00923:未找到预期form关键字
哪位兄弟帮帮忙，应该怎么改，才能得到行数2???



------解决方案--------------------
你查询出来两组，每组是一条，有错吗？

如果想查询总行数 select count(*) over() , ...... 别套来套去的。
------解决方案--------------------

SQL code

select count(*) from (select 字段 from 表名  group by 字段)