Solved

# How to count by outer group in Oracle?

Posted on 2008-09-29
458 Views
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"

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
``````
0
Question by:mawingho
• 2

LVL 73

Accepted Solution

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"
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 73

Expert Comment

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
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

### Suggested Solutions

UNIX SCP 5 49
Convert Oracle data into XML document 2 39
report returning null 21 52
Oracle and DateTime math 6 16
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function