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?

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.

Source (Stackoverflow)