SQL Count Query with Grouping by multiple Rows
- by Christian
I have a table with three filled rows named "Name", "City" and "Occupation".
I want to create a new row in the same table that contains the number of people who have the same occupation.
"Name" | "City" | "Occupation"
------------------------------
Amy | Berlin | Plumber
Bob | Berlin | Plumber
Carol | Berlin | Lawyer
David | London | Plumber
I want to have a table that contains:
"Name" | "City" | "Occupation" | "Number"
---------------------------------------
Amy | Berlin | Plumber | 2
Bob | Berlin | Plumber | 2
Carol | Berlin | Lawyer | 1
David | London | Plumber | 1
How does the SQL Query that creates the new row have to look like? I want to actually create a new row in the database that I can access later.