columns not grouping with multiple columns in the group by with Oracle

I am having issues with a group by in Oracle.  I need to find single agencies with higher then one branch number.
The group by works with just the agency_no, but as soon as I add the second column it no longer groups.  Even if I exclude the having part it still is not returning what I need.


select agency_no, branch_no, count(*) as ct from VIEW_1
group by agency_no, branch_no
having count(*)=1
order by agency_no, branch_no

Agency   Branch  CT
12166      1      1
12171      1      1
12171      2      1
12172      1      1
12174      1      1
12175      1      1

the 12171 should not be listed.
thebabinatorAsked:
Who is Participating?
 
dsackerContract ERP Admin/ConsultantCommented:
That's the beauty of piloting, to help hone our communication. Try this:
SELECT *
FROM   VIEW_1
WHERE  agency_no IN (SELECT agency_no FROM VIEW_1
                     GROUP BY agency_no
                     HAVING COUNT(DISTINCT branch_no) = 1)
AND branch_no > 1
ORDER BY agency_no, branch_no

Open in new window

0
 
dsackerContract ERP Admin/ConsultantCommented:
You may want to try something like this:
select agency_no, COUNT(distinct branch_no) AS branch_cnt
FROM VIEW_1
group by agency_no
HAVING COUNT(distinct branch_no) > 1
ORDER BY agency

Open in new window

0
 
thebabinatorAuthor Commented:
I need to actually see the value for the branch_no.  The point of my problem is that I have agencies with branch numbers starting at 2 or 3 rather then 1 and I have another app that is failing because of it.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
dsackerContract ERP Admin/ConsultantCommented:
If you need to SELECT more information you can JOIN that query this way:
SELECT *
FROM   VIEW_1
WHERE  agency_no IN (SELECT agency_no FROM VIEW_1
                     GROUP BY agency_no
                     HAVING COUNT(DISTINCT branch_no) > 1)
ORDER BY agency_no, branch_no

Open in new window

0
 
thebabinatorAuthor Commented:
Thanks but that's not returning what i need

  (SELECT agency_no FROM VIEW_1 GROUP BY agency_no HAVING COUNT(DISTINCT branch_no) > 1)  
Returns agencies that have more then 1 branch.

I need agencies that only have 1 branch that have a branch_no greater then 1.

I need to group by agency but also including branch as that is what i need to display.
0
 
thebabinatorAuthor Commented:
That works!!!!

Do you know what was wrong with my original group by statement?
0
 
thebabinatorAuthor Commented:
Awesome! thanks for working at it until resolved.
0
 
dsackerContract ERP Admin/ConsultantCommented:
Your original statement was grouping by both agency and branch, whereas it should only group by branch. Then that becomes the IN select statement for the list of agencies.

Glad that helped.
0
 
thebabinatorAuthor Commented:
So oracle does not support multiple group by columns like MS SQL?  I hate going back and forth with the differences.

thanks again.
0
 
dsackerContract ERP Admin/ConsultantCommented:
It does support multiple, but in this case, you needed the list of agencies by themselves, joined to the SELECT * FROM VIEW_1. So it was a bit different.
0
 
PortletPaulfreelancerCommented:
>>"I need agencies that only have 1 branch that have a branch_no greater then 1."

SELECT
    AGENCY, BRANCH, CT
FROM (
      SELECT
            VIEW_1.*
          , COUNT(*) OVER(PARTITION BY AGENCY) AGENCYCOUNT
      FROM VIEW_1
      WHERE BRANCH > 1
    )
WHERE AGENCYCOUNT = 1

Open in new window


That results in:
| AGENCY | BRANCH | CT |
|--------|--------|----|
|  12171 |      2 |  1 |

Open in new window


But in your original question you explicitly state that "the 12171 should not be listed."  however 12171 is the only row that has a branch > 1

Could you re-assess your requirements so they are clear? In particular supply the "expected result"

details:
   CREATE TABLE VIEW_1
        ("AGENCY" int, "BRANCH" int, "CT" int)
    ;
    
    INSERT ALL 
        INTO VIEW_1 ("AGENCY", "BRANCH", "CT")
             VALUES (12166, 1, 1)
        INTO VIEW_1 ("AGENCY", "BRANCH", "CT")
             VALUES (12171, 1, 1)
        INTO VIEW_1 ("AGENCY", "BRANCH", "CT")
             VALUES (12171, 2, 1)
        INTO VIEW_1 ("AGENCY", "BRANCH", "CT")
             VALUES (12172, 1, 1)
        INTO VIEW_1 ("AGENCY", "BRANCH", "CT")
             VALUES (12174, 1, 1)
        INTO VIEW_1 ("AGENCY", "BRANCH", "CT")
             VALUES (12175, 1, 1)
    SELECT * FROM dual
    ;
**Query 1**:

    SELECT
    AGENCY, BRANCH, CT
    FROM (
          SELECT
                VIEW_1.*
              , COUNT(*) OVER(PARTITION BY AGENCY) AGENCYCOUNT
          FROM VIEW_1
          WHERE BRANCH > 1
        )
    WHERE AGENCYCOUNT = 1

**[Results][2]**:
    | AGENCY | BRANCH | CT |
    |--------|--------|----|
    |  12171 |      2 |  1 |

  [1]: http://sqlfiddle.com/#!4/96ce6/4

Open in new window

0
 
PortletPaulfreelancerCommented:
>>"So oracle does not support multiple group by columns like MS SQL? "

NO it does NOT and for GOOD reason!

That MySQL "feature" is very flawed and you should NOT rely on it.
0
 
thebabinatorAuthor Commented:
yes, thats true i wasnt clear enough.

but I was correct in that 12171 should not be listed as it has several branches.  my grouping was failing.  The table shows a sample of my results.

>>"I need agencies that only have 1 branch that have a branch_no greater then 1."
0
 
PortletPaulfreelancerCommented:
In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause.
...
MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause.  ... However, ... The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.
(emphasis added) https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html

Basically this saying that if conditions are ideal it works, but if the conditions are not ideal MySQL will just provide a guess.

Also note that "standard SQL"is the standard, it is MySQL that is at odds to the standard
0
 
dsackerContract ERP Admin/ConsultantCommented:
I could be mistaken, but I think this had nothing to do with MySQL. And Oracle has multiple grouping, although it wasn't necessary with this particular objective.
0
 
thebabinatorAuthor Commented:
I am learning as I go but I mentioned MS SQL not mysql.

and this does work perfectly in MS SQL in a similar data set but I needed it in my app pulling from Oracle.

anyway.  thanks again.
0
 
PortletPaulfreelancerCommented:
@dsacker, my references to MySQL are in response to comments by the author
      "The group by works with just the agency_no, but as soon as I add the second column it no longer groups."
        &
         "So oracle does not support multiple group by columns like MS SQL?  "

In MySQL you can avoid nominating all the columns i.e. this is legal in MySQL

select agency branch, cont(*) from view_1
group by agency


But this is NOT legal in any other RDBMS that supports SQL that I have used (and that's quite a few).
0
 
PortletPaulfreelancerCommented:
I'm so sorry, I misread that, but MS SQL is exactly the same as Oracle with respect to the GROUP BY clause.
You have to specify all non-aggregating columns in both the SELECT and GROUP BY clauses.
0
 
slightwv (䄆 Netminder) Commented:
Even though I'm late to the party and this is already closed, the accepted post accesses the table twice.

This should be unnecessary.

Using the setup by Paul above, this hits the table once and should be more efficient:
    SELECT
    AGENCY, BRANCH, CT
    FROM (
          SELECT
                VIEW_1.*
              , row_number() OVER(PARTITION BY AGENCY order by agency) rn
          FROM VIEW_1
        )
    WHERE rn = 1
/

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.