SQL having 2 types but no more

Question

Having a table with two columns, carrier and networktype I want to select the carrier, networktype and the count of rows that have exactly 2 specific networks types and no others. So given the following table

Carrier     NetworkType     Count
302720      WIFI_11B_G      2
31000       NONE            1
31000       WIFI_11B_G      72
ATT         EDGE            4
ATT         HSPA            757
ATT         UMTS            98
ATT         WIFI_11B_G      163,511
BELL        HSPA            3
BELL        LTE             16
BELL        NONE            57
BELL        WIFI_11B_G      5,866
CHINAMOBILE NONE            2
CHINAMOBILE WIFI_11B_G      37

It should just return:

31000       NONE            1
31000       WIFI_11B_G      72
CHINAMOBILE NONE            2
CHINAMOBILE WIFI_11B_G      37

Here's the query that I'm running

SELECT carrier, network_type, count(*) FROM mytable
GROUP BY carrier, network_type
HAVING SUM(CASE
WHEN network_type = 'NONE' THEN 1
WHEN network_type = 'WIFI_11B_G' THEN 1
ELSE 2
END) = 2
AND network_type IN ('NONE', 'WIFI_11B_G')

but it's returning only the carriers that have exactly 2 counts! so for example 302720 with WIFI_11B_G since it only has 2 counts, but it doesn't have a NONE type. Or CHINAMOBILE with NONE but no WIFI.

I think I'm on the right track with the having clause and I'd like to solve this just with a single query, no sub queries. Thanks!

what are u talking about? no matter how if u want to return just 'None' and 'Wifi' then u shouldnt used IN — Low Chee Mun, Oct 08 '13 at 02:22

score 1 · Accepted Answer · answered Oct 08 '13 at 02:58

You appear to be asking for two specific networks, but want to eliminate anyone who a)doesnt have both or b)has those two AND more. I would approach like

SELECT 
      mt2.Carrier,
      mt2.NetworkType,
      mt2.Count
   from
      ( select carrier,
               SUM(CASE WHEN  network_type IN ( 'NONE', 'WIFI_11B_G')
                        THEN 1 else 0 end ) as NetworksIWant,
               count(*) as AllNetworksForCarrier
           from
               myTable
           group by
               carrier ) PreQuery
      JOIN myTable mt2
         on PreQuery.carrier = mt2.carrier
        AND mt2.network_type IN ( 'NONE', 'WIFI_11B_G')
   where
          PreQuery.NetworksIWant = 2
      AND PreQuery.AllNetworksForCarrier = 2

The inner "PreQuery" gets for each carrier how many match those you ARE interested in, but in addition, counts for ALL for a given carrier. Then, they are removed via the where looking for both sum() and count() = 2.

SQL having 2 types but no more

1 Answers1