COUNT in a JOIN-CodePudding

I do not get what I'm doing wrong. I was asked to use join to count number of events in a state. The tables have similar columns like this:

| date     | location    | city    | state | lat        | lng        |
|----------|-------------|---------|-------|------------|------------|
| mm/dd/yy | Sanford, FL | Sanford | FL    | 28.8028612 | -81.269453 |

I tried bunch of queries but all are returning wrong counts. Here's one query:

SELECT table1.state, count(table2.state)
FROM table1
JOIN table2
ON table1.state = table2.state
GROUP BY table1.state;

Thanks

CodePudding user response：

When you make a join here you are connecting the rows in one table with the rows in another table, so that each row in one table matches up with at most one row in the other table. You can think of it as building a new table on the fly, the rows need to match up, it may be you don't have a match for a row (in which case you can still include it using an outer join) but if your join condition is nonunique you will get duplicate rows as you get back all possible matches.

So figure out a way to join that will produce 1-1 matches based on data shared by the tables. There isn't enough information given in the question to say what columns should be included. It may mean including lat, long, and date in the join conditions, assuming there aren't multiple events on the same day for the same latitude and longitude.

If you include tables and sample data, and provide descriptions of the erroneous results, you have a better chance of getting useful answers.

CodePudding user response：

or you can take a look at