Joining Multiple Tables to Count Results

Question

Relatively new to SQL, so pardon my ignorance if this is a simple solve. I'm using Google Big Query in Standard SQL and I've tried reading up on other topics, but none seemed to fit the need I'm looking for.

I have two different tables that I'm pulling from, and I've built separate sub-queries to pull information into the below format:

• Table A shows me a list of all States serviced, and all Restaurants owned in those cities

• Table B looks at recent sales in each State / Restaurant, each having a unique transaction ID, so there may be multiple times a state/restaurant combo shows up

        (Table A)                        (Table B)                              (Table C)
    State  | Restaurant         State | Restaurant  |Sale ID           State | Restaurant | Transactions
    -------------------         ----------------------------           ---------------------------------
      NY   |  Peter's            NY   | Peter's     |  111  |          NY    | Peter's    |     2
      NY   |  Burger Bin         NY   | Peter's     |  181  |          NY    | Burger Bin |     0
      NY   |  Al's               NY   | Al's        |  925  |          NY    | Al's       |     1
      CA   |  Peter's            CA   | Burger Bin  |  596  |          CA    | Peter's    |     0
      CA   |  Burger Bin         CA   | Burger Bin  |  231  |          CA    | Burger Bin |     2
      CA   |  Al's               IL   | Al's        |  87   |          CA    | Al's       |     0
      IL   |  Peter's            IL   | Burger Bin  |  714  |          IL    | Peter's    |     0
      IL   |  Burger Bin         IL   | Al's        |  12   |          IL    | Burger Bin |     1 
      IL   |  Al's                                                     IL    | Al's       |     2

Where I'm having issue is with joining A & B together into the output that I'd like to see in Table C.

I would like to see all states and restaurants listed, with a column that displays the number of times the combo appeared in Table B. If they didn't appear, I still want them represented, but with a 0 for the value.

I've tried using different count functions, but haven't managed to get the right result, and I think it's that I'm not joining correctly. Not sure how I need to join these, or what functions I need to use.

Any help would be very appreciated!

Answer 1

You want a join and group by. You can use this query for this purpose:

SELECT State, Restaurant, COUNT(TableB.SaleID) as Transactions
FROM TableA
LEFT JOIN TableB
 USING(State, Restaurant)
GROUP BY State, Restaurant

Answer 2

Yet another option for BigQuery Standard SQL

#standardSQL
SELECT State, Restaurant, IFNULL(Transactions, 0) Transactions
FROM `project.dataset.tableA`
LEFT JOIN (
  SELECT State, Restaurant, COUNT(1) AS Transactions
  FROM `project.dataset.tableB`
  GROUP BY State, Restaurant
)
USING(State, Restaurant)   

If to apply to sample data from your question as in below example

#standardSQL
WITH `project.dataset.tableA` AS (
  SELECT 'NY' State, "Peter's" Restaurant UNION ALL
  SELECT 'NY', "Burger Bin" UNION ALL
  SELECT 'NY', "Al's" UNION ALL
  SELECT 'CA', "Peter's" UNION ALL
  SELECT 'CA', "Burger Bin" UNION ALL
  SELECT 'CA', "Al's" UNION ALL
  SELECT 'IL', "Peter's" UNION ALL
  SELECT 'IL', "Burger Bin" UNION ALL
  SELECT 'IL', "Al's" 
), `project.dataset.tableB` AS (
  SELECT 'NY' State, "Peter's" Restaurant, 111 SaleID UNION ALL
  SELECT 'NY', "Peter's", 181 UNION ALL
  SELECT 'NY', "Al's", 925 UNION ALL
  SELECT 'CA', "Burger Bin", 596 UNION ALL
  SELECT 'CA', "Burger Bin", 231 UNION ALL
  SELECT 'IL', "Al's", 87 UNION ALL
  SELECT 'IL', "Burger Bin", 714 UNION ALL
  SELECT 'IL', "Al's", 12 
)
SELECT State, Restaurant, IFNULL(Transactions, 0) Transactions
FROM `project.dataset.tableA`
LEFT JOIN (
  SELECT State, Restaurant, COUNT(1) AS Transactions
  FROM `project.dataset.tableB`
  GROUP BY State, Restaurant
)
USING(State, Restaurant)

output is

Row State   Restaurant      Transactions     
1   NY      Peter's         2    
2   NY      Burger Bin      0    
3   NY      Al's            1    
4   CA      Peter's         0    
5   CA      Burger Bin      2    
6   CA      Al's            0    
7   IL      Peter's         0    
8   IL      Burger Bin      1    
9   IL      Al's            2