How to display output like this in SQL?

Question

I have a table like the one shown below:

+----------------+-------+----------+---------+
| Name           | Model | system   | ItemTag |
+----------------+-------+----------+---------+
| Alarm Id       | T58   | ASC      |         |
+----------------+-------+----------+---------+
| Door Lock      | F48   | ASC      |         |
+----------------+-------+----------+---------+
| AlarmSounder   | T58   | ASC      |         |
+----------------+-------+----------+---------+
| Card Reader    | K12   | ASC      |         |
+----------------+-------+----------+---------+
| Magnetic Lock  | F48   | ASC      |         |
+----------------+-------+----------+---------+
| T2 Card Reader | K12   | ASC      |         |
+----------------+-------+----------+---------+
| Power   Supply | Null  | ASC      |         |
+----------------+-------+----------+---------+
| Battery         |  Null| ASC      |         |
+----------------+-------+----------+---------+

Now I want to display the data like this:

+-------------+-------+--------+--------+
| Name        | Model | system | count  |
+-------------+-------+--------+--------+
| Alarm       | T58   | ASC    | 2      |
+-------------+-------+--------+--------+
| Door Lock   | F58   | ASC    | 2      |
+-------------+-------+--------+--------+
| Card Reader | K12   | ASC    | 2      |
+-------------+-------+--------+--------+
|Power supply | Null   | ASC    | 1     |
+-------------+-------+--------+--------+
| Battery    | Null   | ASC    | 1     |
+-------------+-------+--------+--------+

How to do it in SQL?

Updated I also included null column as my second update.

Answer 1

You could use windowed functions:

SELECT Name, Model, system, cnt AS count 
FROM (SELECT *, COUNT(*) OVER(PARTITION BY Model) AS cnt,
            ROW_NUMBER() OVER(PARTITION BY Model ORDER BY ...) AS rn
      FROM your_tab) AS sub
WHERE rn = 1;

Rextester Demo

Keep in mind that you need a column to sort so (id/timestamp) should be used to get first value in a group.

---

EDIT:

As i have different Name relating to null column. how can i seperate it out

SELECT Name, Model, system, cnt AS count 
FROM (SELECT *, COUNT(*) OVER(PARTITION BY Model) AS cnt,
            ROW_NUMBER() OVER(PARTITION BY Model ORDER BY id) AS rn
      FROM my_tab
      WHERE Model IS NOT NULL) AS sub
WHERE rn = 1
UNION ALL
SELECT Name, Model, system, 1
FROM my_tab
WHERE Model IS NULL;

RextesterDemo 2