October 19, 2011 at 9:08 am
It's too long to explain the business reason for my problem, so I'm using Northwind to illustrate. If you run this:
SELECT [CustomerID],ShipVia, Freight
FROM [Northwind].[dbo].[Orders]
order by CustomerID
You get this (partial listing of results):
CustomerIDShipViaFreight
ALFKI129.46
ALFKI261.02
ALFKI123.94
ALFKI369.53
ALFKI140.42
ALFKI11.21
ANATR339.92
ANATR311.99
ANATR143.90
ANATR31.61
October 19, 2011 at 9:11 am
Apparently it's also too long to post the full question!
October 19, 2011 at 9:13 am
Oops, posted that too early. Anyway, as I was saying, in the Northwind.Orders table I would like to sum the Freight column but CustomerID. I would also like the ShipVia value associated with the larger Freight amount. For ALFKI this is '3', so MAX(ShipVia) would work, but for ANATR the ShipVia = '1' for largest freight order. Plus though ShipVia is datatype INT, the numbers don't really mean anything, it's a nominal variable, so I don't think MIN or MAX is appropriate anyway.
Anyone have ideas?
October 19, 2011 at 9:15 am
Ninja's_RGR'us (10/19/2011)
Apparently it's also too long to post the full question!
I got it out eventually. 🙂
October 19, 2011 at 9:19 am
Well #1 is easy
Select cust, sum() FROM group BY cust
From there you probably could do this :
CROSS <or outer> APPLY (SELECT TOP 1 via, anything else you need FROM order by via desc)
October 19, 2011 at 9:21 am
Amy.G (10/19/2011)
Oops, posted that too early. Anyway, as I was saying, in the Northwind.Orders table I would like to sum the Freight column but CustomerID. I would also like the ShipVia value associated with the larger Freight amount. For ALFKI this is '3', so MAX(ShipVia) would work, but for ANATR the ShipVia = '1' for largest freight order. Plus though ShipVia is datatype INT, the numbers don't really mean anything, it's a nominal variable, so I don't think MIN or MAX is appropriate anyway.Anyone have ideas?
I followed you for a bit and it sounded like a simple group by. But from
I would also like the ShipVia value associated with the larger Freight amount.
to the end you totally lost me. Given the small sample data from your first post what would the output look like?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 19, 2011 at 9:27 am
Well, this is what I came up with (I tend to have flashes of inspiration immediately after posting) --
SELECT o.[CustomerID], ShipVia, Freight, y.Cost
FROM [Northwind].[dbo].[Orders] o
join
(SELECT customerid, MAX(freight) fr1 --Largest freight amt per customer
FROM [Northwind].[dbo].[Orders]
group by CustomerID) z
ON o.CustomerID = z.CustomerID
JOIN
(SELECT [CustomerID],SUM([Freight]) as Cost --Total freight by customer
FROM [Northwind].[dbo].[Orders]
group by CustomerID) y
ON o.CustomerID=y.CustomerID
WHERE Freight = fr1
order by CustomerID
But will try the TOP 1 suggestion above, see which one is best using the real table.
October 19, 2011 at 9:32 am
Top 1 is "easier" because you can get all the columns you need without going back again to the base table.
Another version would be with rownumber but top works nicely. I've never compared those perf so I can't help there.
October 19, 2011 at 10:01 am
There's another approach that edges out the CROSS APPLY approach. Depending on your indexes, this approach might be better.
PARTITIONED AGGREGATE
WITH OrdersRanked AS (
SELECT [CustomerID],ShipVia
, Sum(Freight) OVER( PARTITION BY CustomerID ) AS FreightTotal
, Row_Number() OVER( PARTITION BY CustomerID ORDER BY Freight DESC ) AS rn
FROM [Northwind].[dbo].[Orders]
)
SELECT CustomerID, ShipVia, FreightTotal
FROM OrdersRanked AS o
WHERE rn = 1
CROSS APPLY
WITH OrdersTotaled AS (
SELECT o.CustomerID
, Sum(o.Freight) AS FreightTotal
FROM Orders AS o
GROUP BY o.CustomerID
)
SELECT o.CustomerID, o2.ShipVia, o.FreightTotal
FROM OrdersTotaled AS o
CROSS APPLY ( SELECT TOP (1) ShipVia FROM Orders AS o2 WHERE o2.CustomerID = o.CustomerID ORDER BY o2.Freight DESC ) AS o2
Here are the stats:
============== PARTITIONED AGGREGATE ==============
Table 'Worktable'. Scan count 3, logical reads 2020, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
============== CROSS APPLY ==============
Table 'Worktable'. Scan count 89, logical reads 1871, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 2, logical reads 44, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 19, 2011 at 10:11 am
drew.allen (10/19/2011)
Sum(Freight) OVER( PARTITION BY CustomerID ) AS FreightTotal
I feel silly to admit this, but I had no idea you could do a sum function like this. This opens up whole new possibilities! Thank you so much! :w00t:
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply