[SQL] Count the number of group (GROUP BY / JOIN / LEFT JOIN / SUM)

<1>

-- subQ: Only MacBookPro user's data
-- mainQ: event name, event freq -> DESC order

SELECT event_name, COUNT(event_name) AS event_freq
FROM (SELECT * 
        FROM playbook_events
        WHERE device = 'macbook pro') AS MBP_user
GROUP BY event_name
ORDER BY event_freq DESC;

 

 

 

 

<2>

--1.left join with 'host_id'
--2.make group by 'nationality'
--3.count n_beds of each groups
--4. sort avail_beds DESC

SELECT nationality, SUM(n_beds) AS avail_beds
FROM (select AA.host_id, nationality, n_beds
    from airbnb_apartments as AA
    left join airbnb_hosts as AH
    on AA.host_id = AH.host_id) total_airbnb
GROUP BY nationality
ORDER BY avail_beds DESC;

* When using JOIN, you have to specify the column names in SELECT query.

  Otherwise, a conflict can occur in the query.

  (And write in SELECT AA.host_id, AH.nationality, AA.n_beds is recommended for defining clearly. (but I didn't))