GROUP BY isn't working as expected

Question

Here is my sample table with only a bit of info.

select * from juniper_fpc';       
    id     |  router   |     part_name      
-----------+-----------+--------------------
 722830939 | BBBB-ZZZ1 | MPC-3D-16XGE-SFPP 
 722830940 | BBBB-ZZZ1 | MPC-3D-16XGE-SFPP 
 723103163 | AAAA-ZZZ1 | DPCE-R-40GE-SFP   
 723103164 | AAAA-ZZZ1 | MPC-3D-16XGE-SFPP 
 723103172 | AAAA-ZZZ1 | DPCE-R-40GE-SFP   
 722830941 | BBBB-ZZZ1 | MPC-3D-16XGE-SFPP

What I'm trying to do is identify elements from the router column that only have a part_name entry beginning with MPC. What I've come up with is this but it's wrong because it lists both of the elements above.

SELECT   router
FROM     juniper_fpc
WHERE    part_name LIKE 'MPC%'
GROUP BY router
ORDER BY router;
  router   
-----------
 AAAA-ZZZ1
 BBBB-ZZZ1

I dont see anything wrong with your code or your output, according to u, both should be returned! — sagi, Feb 03 '16 at 16:01
Your input data contains `| AAAA-ZZZ1 | MPC-3D-16XGE-SFPP|` and `| BBBB-ZZZ1 | MPC-3D-16XGE-SFPP|`, so PostgreSQL returns the correct result. — Frank Schmitt, Feb 03 '16 at 16:02
Do u need the records that ONLY have a part_name like 'MPC' and not others? — Galma88, Feb 03 '16 at 16:03
Maybe you want to see MPC-3D-16XGE-SFPP no its router 'name' (BBBB-ZZZ1 )? In that case you should GROUP BY and ORDER BY part_name . — Jessica, Feb 03 '16 at 16:07

Frank Schmitt · Answer 1 · 2016-02-04T09:33:55.413

Assuming you want the routers that only have part_name like 'MPC%', you can use a conditional count:

select * from (
  select router, 
    count(case when part_name like 'MPC%' then 1 else null end) as cnt_mpc,
    count(*) as cnt_overall
  from juniper_fpc  
  group by router) v_inner
where cnt_mpc = cnt_overall

This can be written more compact (albeit slightly less readable) as

  select router
  from juniper_fpc  
  group by router
  having count(case when part_name like 'MPC%' then 1 else null end)  = count(*)

SQL Fiddle

score 2 · Accepted Answer · edited May 23 '17 at 12:31

This should perform well:

SELECT j1.router
FROM  (
   SELECT   router
   FROM     juniper_fpc
   WHERE    part_name LIKE 'MPC%'
   GROUP    BY router
   ) j1
LEFT   JOIN juniper_fpc j2 ON j2.router = j1.router
                          AND j2.part_name NOT LIKE 'MPC%'
WHERE  j2.router IS NULL
ORDER  BY j1.router;

@sagi's idea with NOT EXISTS whould work, too, if you get it right:

SELECT router
FROM   juniper_fpc j
WHERE  NOT EXISTS (
   SELECT 1
   FROM   juniper_fpc
   WHERE  router = j.router
   AND    part_name NOT LIKE 'MPC%'
   )
GROUP  BY router
ORDER  BY router;

Details:

Select rows which are not present in other table

SQL Fiddle.

Or, @Frank's idea with syntax for Postgres 9.4 or later:

SELECT router
FROM   juniper_fpc
GROUP  BY router
HAVING count(*) = count(*) FILTER (WHERE part_name LIKE 'MPC%')
ORDER  BY router;

Best with an index on (router, partname) for each of them.

score 0 · Answer 3 · answered Feb 03 '16 at 16:07

0

If what you meant was to select only those routers, that all of their part names start with MPC then your query should be:

SELECT   s.router
FROM     juniper_fpc s
WHERE    NOT EXISTS(select distinct id from juniper_fpc t
                    where t.id = s.id part_name NOT LIKE 'MPC%')
GROUP BY s.router
ORDER BY s.router;

answered Feb 03 '16 at 16:07

sagi

40,026
6
59
84

Problems: You cannot refer to the un-aggregated `s.id` in the subquery; `distinct` is no use; missing `AND`. – Erwin Brandstetter Feb 03 '16 at 16:53

score 0 · Answer 4 · answered Feb 03 '16 at 17:06

You could also you Window Functions here:

SELECT
    *
FROM
    (
        SELECT 
            router,
            part_name,
            COUNT(distinct part_name) OVER (PARTITION BY router) as count_of_distinct_parts
        FROM juniper_fpc
    )subqry
WHERE part_name like 'MPC%' AND count_of_distinct_parts = 1

This will open the doors to more complex conditions should this query scope expand.

GROUP BY isn't working as expected

4 Answers4