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.

What's the logic? Why does `AlarmSounder`, `Magnetic Lock` and `T2 Card Reader` are not in the output? — Zohar Peled, Aug 23 '17 at 07:21
Still doesn't help. How can you tell the difference between rows you want to return and rows you don't want to return? — Zohar Peled, Aug 23 '17 at 07:25
Why ( 'Alarm Id' , 'AlarmSounder') => 'Alarm' but ('Door Lock', 'Magnetic Lock') => 'Door Lock' ? — Serg, Aug 23 '17 at 07:30
@Serg , This from our purchase team and they stored data like this — aryan, Aug 23 '17 at 07:32
I see, but the question is about the query. What is the rule which transforms `name` as it is shown in the question? — Serg, Aug 23 '17 at 07:35
If you want ' Alarm Id' in the output realy, please edit your question. — Serg, Aug 23 '17 at 07:39
@serg , Alarm is fine for but i want to return maximum matching rows from similar strings — aryan, Aug 23 '17 at 08:09
This is my question exactly , what is the rule of extracting ' maximum matching' from a set of names? Why ('Door Lock', 'Magnetic Lock') => 'Door Lock' for example ? It doesn't look like extracting the matching part. — Serg, Aug 23 '17 at 08:13
This has lot to do with the spare parts of the devices. So our procurement team is following this model for their records. — aryan, Aug 23 '17 at 08:18

Lukasz Szozda · Accepted Answer · 2017-08-23T08:25:45.307

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

edited Aug 23 '17 at 08:25

answered Aug 23 '17 at 07:20

Lukasz Szozda

162,964
23
234
275

this is almost perfect.. All the rows are returing with the total But i have issues in null column . As i have different Name relating to null column. how can i seperate it out – aryan Aug 23 '17 at 07:33
@aryan Now you are extending your question. I suggest to ask new question with sample data and result. You could use my http://rextester.com to provide all necessary data. – Lukasz Szozda Aug 23 '17 at 07:35
2

@aryan: You need to include all scenarios in question because we try to construct query based on data provided in question. There is no nulls in your sample data. – zarruq Aug 23 '17 at 07:41
@lad2025 , Updated – aryan Aug 23 '17 at 08:17

score 1 · Answer 2 · answered Aug 23 '17 at 07:36

You can have a simple query as below

SELECT MIN(Name) Name, 
       Model, 
       system, 
       COUNT(*) [count]
  FROM yourtable
 GROUP BY Model, system

Result

Name        Model   system  count
Door Lock   F58     ASC     2
Card Reader K12     ASC     2
Alarm Id    T58     ASC     2

score 1 · Answer 3 · answered Aug 25 '17 at 12:27

1

lad2025's solution simplified, calculate both NULL and NOT NULL in a single step and add some logic for the NULL rows:

SELECT Name, Model, system, 
   CASE WHEN Model IS NULL THEN 1 ELSE cnt END AS count 
FROM
 (
   SELECT *, 
      COUNT(*) OVER(PARTITION BY Model) AS cnt,
      ROW_NUMBER() OVER(PARTITION BY Model ORDER BY Name) AS rn
   FROM my_tab
 ) AS sub
WHERE rn = 1         -- one row per model 
   OR Model IS NULL; -- all rows for the NULL model

answered Aug 25 '17 at 12:27

dnoeth

59,503
4
39
56

i didnt get the case statement part ..0CASE WHEN Model IS NULL THEN 1 Its saying when model is null then 1 ,which one its pointing here. – aryan Aug 25 '17 at 16:56
@aryan: This simply replaces the count of the NULL rows with 1 to match your expected result :-) – dnoeth Aug 25 '17 at 17:10
yes. i replaced the case with not null and saw what it actually doing in the query. thanks for that . – aryan Aug 25 '17 at 17:24

How to display output like this in SQL?

3 Answers3