Espen Espen - 1 month ago 21
SQL Question

Counting on multiple columns

I have a table like this:

+------------+---------------+-------------+
|store_number|entrance_number|camera_number|
+------------+---------------+-------------+
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 2 | 1 | 1 |
| 2 | 2 | 1 |
| 2 | 2 | 2 |
| 3 | 1 | 1 |
| 4 | 1 | 1 |
| 4 | 1 | 2 |
| 4 | 2 | 1 |
| 4 | 3 | 1 |
+------------+---------------+-------------+


In summary the stores are numbered 1 and up, the entrances are numbered 1 and up for each store, and the cameras are numbered 1 and up for each entrance.

What I want to do is count how many how many entrances in total, and how many cameras in total for each store. Producing this result from the above table:

+------------+---------------+-------------+
|store_number|entrances |cameras |
+------------+---------------+-------------+
| 1 | 1 | 2 |
| 2 | 2 | 3 |
| 3 | 1 | 1 |
| 4 | 3 | 4 |
+------------+---------------+-------------+


How can I count on multiple columns to produce this result?

Answer

You can do this with a GROUP BY and a COUNT() of each item:

Select   Store_Number, 
         Count(Distinct Entrance_Number) as Entrances, 
         Count(Camera_Number) As Cameras
From     YourTable
Group By Store_Number

From what I can tell from your expected output, you're looking for the number of cameras that appear, whilst also looking for the DISTINCT number of entrances.

Comments