Combine rows help. Rows are the exact same

Question

All of my queries produce data at the item level. When I want to run reports across several months, I often run out of rows in excel due to this problem.

IF a customer orders 5 shirts, and they are shipped in the same package, my query produces 5 rows of the exact same data. I don't care what the customer ordered, I just want to know that 1 package went out. Possibly containing 5 units (Qty in the query).

I tried using the "max" function but the query failed.

select 

p.TrackingNumber,
oh.BusinessUnitCode,
cc.Qty,
oh.ShipCode,
p.ContainerID,
convert(date,oh.ShipTime) as 'OrderDate',
oh.OrderNumber 

from dmhost.tblOrderHeader oh

join dmhost.tblContainer c on oh.OrderHeaderID = c.OrderHeaderID
join dmhost.tblPackage p on c.ContainerID = p.ContainerID
join dmhost.tblContainerContents cc on c.ContainerID = cc.ContainerID
join dmhost.tblItemMaster im on im.ItemMasterID = cc.ItemMasterID

where (oh.ShipTime between '2018-05-01' and '2018-05-16')

and cc.Qty <> 0

order by p.ContainerID

The results produces several rows with all of the EXACT same information. The reason for this, is because the customer ordered several items, and they were all shipped in the same package. I only want one line of data for each package.

My knowledge of MySQL dialect is limited, but in ANSI SQL it should be `select distinct...` — mazaneicha, May 31 '19 at 19:43

score 1 · Answer 1 · answered May 31 '19 at 19:15

This is a job for GROUP BY. You haven't provided enough information to tell us exactly what you want in each result row. So this is a guess:

SELECT oh.BusinessUnitCode,
       SUM(cc.Qty) Qty,
       COUNT(*) OrderCount,
       oh.ShipCode,
       p.ContainerID,
       convert(date,oh.ShipTime) as 'OrderDate'
  FROM whatever tables and joins
 WHERE whatever filters
 GROUP BY oh.BusinessUnitCode,
       oh.ShipCode,
       p.ContainerID,
       convert(date,oh.ShipTime)

This summarizes your detail rows.

Combine rows help. Rows are the exact same

1 Answers1