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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
PortletPaulEE Topic AdvisorCommented:
>>"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
PortletPaulEE Topic AdvisorCommented:
>>"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
PortletPaulEE Topic AdvisorCommented:
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
PortletPaulEE Topic AdvisorCommented:
@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
PortletPaulEE Topic AdvisorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.