Syntax question
-
I have a robust query that returns a dataset and the data is good, however some of the records contain the exact same data with the exception of the 'Price' field. I want to combine the records that are identical and SUM the values in the 'Price' field. My query and example return dataset is below.
Query:
--------
select distinct
'On-Demand' as 'Business Line',
o.OrderID as 'Order #',
isnull(d.DisplayCode,'UNK') as Hub,
isnull(rz.RouteID,'UNK') as 'Default Route',
'On-Demand' as 'Assigned Route',
o.DestAddress,
o.DestZip,
C.LocalName,
C.CustID,
DATENAME(dw,o.OrderDate) as 'DoW',
DATENAME(ww,o.OrderDate) as 'Week',
o.Pieces,
OI.Price AS '$',
ct.CustType,
'Everything Else' as 'Code Section'
from tblOrder o
left outer join tblZones z on o.Destzip = z.zip
left outer join tblZonesRouteZones rz on z.ZoneID = rz.ZoneID and rz.RouteLookupID = 1
left outer join tblDepots don z.DepotID = d.DepotID
join tblCustomer c on o.CustID = c.CustID
join tblCustomerTypes ct on c.CustType = ct.CustTypeID
INNER JOIN tblOrderItems OI ON OI.OrderID = CAST(O.OrderID AS VARCHAR(10))
where o.OrderDate >= @StartDate
and o.OrderDate <= @EndDate
and o.Pieces >= '1'
and o.CustID in (750,3103,2835,718,3580,3578,3579,3430,3388,3577,2473,3653,3655,3654,13,1661,3603,3676,3662,3665,3586,3597,1536,3593,979,3554,551,3247,1715,1388,559,3650,3652,3651,3613,2732,3619,3165,3295,2980,3301,1179,33,3379,633,3673,3362,3478,9997,3218,3336,3342,2704,3484,3510,3614,2868,3644,3636,3642,3516,3424,3401,3646,3530,3435,3506,3539,3542,3536,3520,3535,3591,3524,3596,3538,3504,3513,3635,3502,3525,3511,3518,3515,3519,3574,3517,3531,3638,3640,3643,3571,3523,3526,3505,3595,3537,3639,3503,3572,3521,3512,3634,3564,3570,3514,3499,3522,3529,3507,3540,3645,3637,3532,3641)
and o.DestZip like '[3][2-9]%' -- First FL zip code is 32003
and c.LocalName <> 'KMart Pharmacy'
and o.DestAddress <> '5002 W Nassau St'
and OI.Price >= '.01'
Order by "Address","LocalName", "Default Route"
Dataset Returned:
---------------------
-
Hi,
you can use grouping to find sum of Prices over the required set of columns (all except OI.Price)
..maybe something like...
SELECT --without DISTINCT ...
...
DATENAME(ww,o.OrderDate) as 'Week',
o.Pieces,
SUM(OI.Price) AS '$',
ct.CustType,
'Everything Else' as 'Code Section'
..
..
GROUP BY o.OrderID
,d.DisplayCode
,rz.RouteID
,o.DestAddress
,o.DestZip
,C.LocalName
,C.CustID
,o.OrderDate
,o.Pieces
,ct.CustType
... or
SELECT DISTINCT....
...
DATENAME(ww,o.OrderDate) as 'Week',
o.Pieces,
SUM(OI.Price) OVER(PARTITION BY o.OrderID
,d.DisplayCode
,rz.RouteID
,o.DestAddress
,o.DestZip
,C.LocalName
,C.CustID
,o.OrderDate
,o.Pieces
,ct.CustType ORDER BY (SELECT NULL)) AS '$',
ct.CustType,
'Everything Else' as 'Code Section'
..
Dean
D.Mincic
😀
MCTS Sql Server 2008, Database Development -
Dean,
Thank you SO much for the excellent reply! I used your 2nd example and the result is perfect.
1,000 thanks!!!
Robert
-
It might be more efficient to aggregate just the data you need to:
select --distinct'On-Demand' as 'Business Line',
o.OrderID as 'Order #',
isnull(d.DisplayCode,'UNK') as Hub,
isnull(rz.RouteID,'UNK') as 'Default Route',
'On-Demand' as 'Assigned Route',
o.DestAddress,
o.DestZip,
C.LocalName,
C.CustID,
DATENAME(dw,o.OrderDate) as 'DoW',
DATENAME(ww,o.OrderDate) as 'Week',
o.Pieces,
x.Price AS '$',
ct.CustType,
'Everything Else' as 'Code Section'
FROM tblOrder o
left outer join tblZones z on o.Destzip = z.zip
left outer join tblZonesRouteZones rz on z.ZoneID = rz.ZoneID and rz.RouteLookupID = 1
left outer join tblDepots d on z.DepotID = d.DepotID
join tblCustomer c on o.CustID = c.CustID
join tblCustomerTypes ct on c.CustType = ct.CustTypeID
CROSS APPLY (
SELECT Price = SUM(Price)
FROM tblOrderItems OI
WHERE OI.OrderID = CAST(O.OrderID AS VARCHAR(10))
AND OI.Price >= '.01')
) x
where o.OrderDate >= @StartDate
and o.OrderDate <= @EndDate
and o.Pieces >= '1'
and o.CustID in (750,3103,2835,718,3580,3578,3579,3430,3388,3577,2473,3653,3655,3654,13,1661,3603,3676,3662,3665,3586,3597,1536,3593,979,3554,551,3247,1715,1388,559,3650,3652,3651,3613,2732,3619,3165,3295,2980,3301,1179,33,3379,633,3673,3362,3478,9997,3218,3336,3342,2704,3484,3510,3614,2868,3644,3636,3642,3516,3424,3401,3646,3530,3435,3506,3539,3542,3536,3520,3535,3591,3524,3596,3538,3504,3513,3635,3502,3525,3511,3518,3515,3519,3574,3517,3531,3638,3640,3643,3571,3523,3526,3505,3595,3537,3639,3503,3572,3521,3512,3634,3564,3570,3514,3499,3522,3529,3507,3540,3645,3637,3532,3641)
and o.DestZip like '[3][2-9]%' -- First FL zip code is 32003
and c.LocalName <> 'KMart Pharmacy'
and o.DestAddress <> '5002 W Nassau St'
Order by "Address", "LocalName", "Default Route" -- Ordering by literals?
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Thank you for the additional suggestion! I can't tell you how much I appreciate it. I'm still learning SQL and have a long way to go it seems.
Yes, I sort using literal terms due to using several UNION commands to join several similar queries. An example of the reason is in query 1, the address is in a field called "Address", but in query 2 it is called "DestAddress". So, I just map each to the same field name and sort on that name. It may not be the best way, but it's the only way I could make it work. If there's a better way, I'd love to know it.
Thanks again!
Robert
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply