Solved

How to count by outer group in Oracle?

Posted on 2008-09-29
2
475 Views
Last Modified: 2013-11-11
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
Comment
Question by:mawingho
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 22608924
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 22608945
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question