Need query for this Output

Question

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

Nitin Sontakke · 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 Sontakke · Answer

@Ankit please be very polite in your language. We are here to help you. We are not appearing for the exam conducted by you.

Nitin Sontakke · Answer

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

Nitin Sontakke · Answer

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 Sontakke · Answer

@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 P · Answer

select carton_noitemno  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 Shukla · Answer

Thank you All of you. Thank you Nitin Sir. I think your answer is helpful for me after ignoring location.

Joginder Banger · Answer

@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 Sontakke · Answer

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 Shukla · Answer

If you want you can avoid location. main fields are carton item and qnty.

Ankit Shukla · Answer

Sorry Nitin Ji I have no intention to hurt any one. Please dont misunderstand my words. All of you are respected for me.

Nitin Sontakke · Answer

@Joginder I noticed that immediately. Hence my second post.

Joginder Banger · Answer

@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 Shukla · Answer

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 P · Answer

AD/00003/ER FLOWER BLOCK2 3 which will not added to BLOCK 4 group

Joginder Banger · Answer

can you explore more how get the quantity of 7 in first rows

Rafnas T P · Answer

try this select carton_noitemno location_code sum (quantity) as quantity group by quantity carton_no itemno  location_code