Question 206340 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql Select statement with GROUP BY

Hi this is my select sql query and its result :

SELECT line,machine_type, COUNT(*) as linewise FROM dr_scan GROUP BY machine_type,line ORDER BY dr_scan.line ASC

line machine_type linewise
1 Button Attach 4
1 Double Needle 1
1 Flatlock 1
1 Single Needle 1
5 Button Attach 2
5 Flatlock 1
5 Double Needle 1

I want to make this table as below

line machine_type linewise
1 Button Attach 4
Double Needle 1
Flatlock 1
Single Needle 1
5 Button Attach 2
Flatlock 1
Double Needle 1
Tags:

2

Answers


  1. 0

    use lag to get the previous line sort by line and put it in a case statement such that if the value of lag is equal to line than display blank.

    insert into @tmp(
line ,  machine_type ,  linewise 
)
values(1,'Button Attach',   4)
,(1,'Double Needle',    1)
,(1,'Flatlock', 1)
,(1,'Single Needle',    1)
,(5,'Button Attach',    2)
,(5,'Flatlock', 1)
,(5,'Double Needle',    1)

select 
case when lag(line) over(order by line)=line then '' else cast(line as varchar(10)) end line
,machine_type,linewise
from @tmp

    output:

    line    machine_type    linewise
1   Button Attach   4
    Double Needle   1
    Flatlock    1
    Single Needle   1
5   Button Attach   2
    Flatlock    1
    Double Needle   1
    Login or Signup to reply.
  2. 0

    You can replace the SELECT for line by the following, using row_number() = 1

    SELECT CASE WHEN 
ROW_NUMBER() OVER (PARTITION BY line ORDER BY dr_scan.line) = 1 
THEN line ELSE NULL END
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search