Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • Last Modified:

How to count by outer group in Oracle?

How to count by outer group? I mean
when I use count now, it count the number of records in the group po.po_nbr, pt.pt_part.

By I just want to count the group po.po_nbr and appear in the "Number of Record" column, how to do that?
SELECT po.po_nbr "PO Number", pt.pt_part "Part Number", COUNT(pt.pt_part) "Number of Record", CEIL(COUNT(pt.pt_part) / 9) "Total Page"
FROM {mfgotrng}.QAD.PO_MSTR po, {mfgotrng}.QAD.POD_DET pod, {mfgotrng}.QAD.PT_MSTR pt
WHERE po.po_domain = pod.pod_domain AND pod.pod_domain = pt.pt_domain AND po.po_nbr = pod.pod_nbr AND pod.pod_part = pt.pt_part
AND pod.pod_type <> 'm'
GROUP BY po.po_nbr, pt.pt_part

Open in new window

0
mawingho
Asked:
mawingho
  • 2
1 Solution
 
sdstuberCommented:
SELECT po.po_nbr "PO Number", pt.pt_part "Part Number", COUNT(*) over (partition by po.po_nbr) "Number of Record" , CEIL(COUNT(pt.pt_part) / 9) "Total Page"
FROM {mfgotrng}.QAD.PO_MSTR po, {mfgotrng}.QAD.POD_DET pod, {mfgotrng}.QAD.PT_MSTR pt
WHERE po.po_domain = pod.pod_domain AND pod.pod_domain = pt.pt_domain AND po.po_nbr = pod.pod_nbr AND pod.pod_part = pt.pt_part
AND pod.pod_type <> 'm'
0
 
sdstuberCommented:
or maybe this...

select  po_nbr "PO Number", pt_part "Part Number", cnt "Number of Record", CEIL(cnt / 9) "Total Page"
from
(
SELECT po.po_nbr , pt.pt_part , COUNT(*) over (partition by po.po_nbr) cnt
FROM {mfgotrng}.QAD.PO_MSTR po, {mfgotrng}.QAD.POD_DET pod, {mfgotrng}.QAD.PT_MSTR pt
WHERE po.po_domain = pod.pod_domain AND pod.pod_domain = pt.pt_domain AND po.po_nbr = pod.pod_nbr AND pod.pod_part = pt.pt_part
AND pod.pod_type <> 'm')
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now