Home Ask Login Register

Developers Planet

Your answer is one click away!

Espen November 2016

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?

Answers


Siyual November 2016

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

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

Post Status

Asked in November 2016
Viewed 3,635 times
Voted 14
Answered 0 times

Search




Leave an answer


Quote of the day: live life