Need query for this Output

Ankit Shukla

9y966SQL

Hello,

I have a table named "Shipment":

carton_no itemno location_code quantity

AD/00005/ER FLOWER2 BLOCK 4 6

AD/00007/ER FLOWER2 BLOCK 4 1

AD/00003/ER FLOWER BLOCK 4 4

AD/00004/ER FLOWER BLOCK 4 4

AD/00005/ER FLOWER BLOCK 4 4

AD/00003/ER FLOWER2 BLOCK 4 6

AD/00003/ER FLOWER BLOCK2 3

AD/00005/ER FLOWER BLOCK 4 4

I need output:

carton_no itemno location_code quantity

AD/00003/ER FLOWER BLOCK 4 7

AD/00003/ER FLOWER2 BLOCK 4 6

AD/00004/ER FLOWER BLOCK 4 4

AD/00005/ER FLOWER BLOCK 4 8

AD/00005/ER FLOWER2 BLOCK 4 6

AD/00007/ER FLOWER2 BLOCK 4 1

Thanks in Advance

17 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke9y ago
Accepted answer
For the output you want, you will need to leave out the location_code. If you really want it in the output, put the hard coded value:
declare @shipment table
(
  carton_no varchar(20)
  ,itemno  varchar(20)
  ,location_code varchar(20)
  ,quantity integer
)

insert into @shipment values
('AD/00005/ER', 'FLOWER2', 'BLOCK 4', 6),
('AD/00007/ER', 'FLOWER2', 'BLOCK 4', 1),
('AD/00003/ER', 'FLOWER ', 'BLOCK 4', 4),
('AD/00004/ER', 'FLOWER ', 'BLOCK 4', 4),
('AD/00005/ER', 'FLOWER ', 'BLOCK 4', 4),
('AD/00003/ER', 'FLOWER2', 'BLOCK 4', 6),
('AD/00003/ER', 'FLOWER ', 'BLOCK2 ', 3),
('AD/00005/ER', 'FLOWER ', 'BLOCK 4', 4)

select carton_no
  ,itemno
,'BLOCK 4' location_code
,sum(quantity) quantity
from @shipment
group by
  carton_no
,itemno
order by 1, 2
Nitin Sontakke9y ago
@Joginder, location_code is the only culprit. See another post where i removed location_code.

AD/00003/ER FLOWER BLOCK 4 7
AD/00003/ER FLOWER2 BLOCK 4 6
AD/00004/ER FLOWER BLOCK 4 4
AD/00005/ER FLOWER BLOCK 4 8
AD/00005/ER FLOWER2 BLOCK 4 6
AD/00007/ER FLOWER2 BLOCK 4 1
Rafnas T P9y ago
select carton_no,itemno ,sum(quantity) as quantity
group by quantity, carton_no, itemno
if you dont want column loaction code then dont put in select statement so that group by will work
Ankit Shukla9y ago
Thank you All of you. Thank you Nitin Sir. I think your answer is helpful for me after ignoring location.
Joginder Banger9y ago
@Nitin in your answer does not mean a location_code. If i remove the location_code same output is that correct?

I think may be inserted data wrong or may be @ankit logic wrong.
Nitin Sontakke9y ago
I can imagine. I already knew that you didn't mean it.

The problem is English is not our first language. That's why such mistakes happen.

I wanted to point out because this is an International forum and someone non-Indian responding your question will take it offensively. So be extremely careful while constructing the sentenses.
Ankit Shukla9y ago
If you want, you can avoid location. main fields are carton, item and qnty.
Ankit Shukla9y ago
Sorry Nitin Ji, I have no intention to hurt any one. Please dont misunderstand my words. All of you are respected for me.
Nitin Sontakke9y ago
@Joginder, I noticed that immediately. Hence my second post.
Nitin Sontakke9y ago
@Ankit, please be very polite in your language. We are here to help you. We are not appearing for the exam conducted by you.
Joginder Banger9y ago
@Nitin I think not match with reqirements
AD/00003/ER FLOWER  BLOCK 4    4
AD/00003/ER FLOWER  BLOCK2     3
AD/00003/ER FLOWER2 BLOCK 4    6
AD/00004/ER FLOWER  BLOCK 4    4
AD/00005/ER FLOWER  BLOCK 4    8
AD/00005/ER FLOWER2 BLOCK 4    6
AD/00007/ER FLOWER2 BLOCK 4    1
Ankit Shukla9y ago
Sorry but All the answers are wrong.

Sum of quantity is based on items. And there are multiple items in a carton. thats why

Carton Item Location Qnty
AD/00003/ER FLOWER BLOCK 4 7 (4 + 3)
AD/00003/ER FLOWER2 BLOCK 4 6
Rafnas T P9y ago
AD/00003/ER FLOWER BLOCK2 3 which will not added to
BLOCK 4 group
Joginder Banger9y ago
can you explore more how get the quantity of 7 in first rows
Rafnas T P9y ago
try this
select carton_no,itemno ,location_code,sum(quantity) as quantity
group by quantity, carton_no, itemno , location_code
Nitin Sontakke9y ago
Looks like you have added BLOCK2 quantity to the BLOCK4 quantity. That will not be possible. If you want to do that, leave the location_code column out of equation.

Simple, you cannot ignore a column at the time of sum but have it in the output. Either ignore in both or include in both.
Nitin Sontakke9y ago
I think it is a simple sum / group by query. Or is there any twist?

select carton_no
,itemno
,location_code
,sum(quantity) quantity
from shipment
group by
carton_no
,itemno
,location_code
order by 1, 2