asked on

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.

dsacker

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

thebabinator

ASKER

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.

dsacker

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

thebabinator

ASKER

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.

ASKER CERTIFIED SOLUTION

dsacker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thebabinator

ASKER

That works!!!!

Do you know what was wrong with my original group by statement?

thebabinator

ASKER

Awesome! thanks for working at it until resolved.

dsacker

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.

thebabinator

ASKER

So oracle does not support multiple group by columns like MS SQL? I hate going back and forth with the differences.

thanks again.

dsacker

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.

PortletPaul

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

PortletPaul

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

thebabinator

ASKER

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

PortletPaul

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

dsacker

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.

thebabinator

ASKER

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.

PortletPaul

@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).

PortletPaul

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.

slightwv (䄆 Netminder)

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