Select SUM of Select MAX | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Select SUM of Select MAX

<br />SELECT Item_Id, MAX(Item_Price) AS MaxPrice<br />FROM tbl_Item_Price<br />WHERE (Category_Code = N’TCA776′) <br /> AND (Item_Date &gt;= CONVERT(DATETIME, ‘2007-03-06 00:00:00’, 102)) <br /> AND (Item_Date &lt;= CONVERT(DATETIME, ‘2007-03-07 23:59:59′, 102))<br />GROUP BY Item_Id<br />ORDER BY Item_Id<br /><br /><br />This works fine, giving me the highest price for each item within the given date range.<br /><br />Now I need the sum of all the rows returned.<br /><br />This is what I thought might work, but it DOESN’T:<br /><br /><font color="red">SELECT SUM(MaxPrice) <br />FROM</font id="red"><br />SELECT Item_Id, MAX(Item_Price) AS MaxPrice<br />FROM tbl_Item_Price<br />WHERE (Category_Code = N’TCA776’) <br /> AND (Item_Date &gt;= CONVERT(DATETIME, ‘2007-03-06 00:00:00’, 102)) <br /> AND (Item_Date &lt;= CONVERT(DATETIME, ‘2007-03-07 23:59:59′, 102))<br />GROUP BY Item_Id<br />ORDER BY Item_Id<br /><br /><br />Anyone see what I’m trying to do and know how to do it? I thought this would be easy! <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br /><br /><br />Much thanks!<br /><br />Kirby<br />
Try the following… SELECT SUM(MaxPrice)
FROM
(SELECT Item_Id, MAX(Item_Price) AS MaxPrice
FROM tbl_Item_Price
WHERE (Category_Code = N’TCA776′)
AND (Item_Date >= CONVERT(DATETIME, ‘2007-03-06 00:00:00’, 102))
AND (Item_Date <= CONVERT(DATETIME, ‘2007-03-07 23:59:59’, 102))
GROUP BY Item_Id) a
MohammedU.
Moderator
SQL-Server-Performance.com
1. You don’t have to convert the data to datetime
2. don’t assume 23:59:59 covers the entire day. SELECT SUM(MaxPrice)
FROM
(
SELECT Item_Id, MAX(Item_Price) AS MaxPrice
FROM tbl_Item_Price
WHERE (Category_Code = N’TCA776′)
AND (Item_Date >= CONVERT(DATETIME, ‘2007-03-06 00:00:00’, 102))
AND (Item_Date <= CONVERT(DATETIME, ‘2007-03-07 23:59:59’, 102))

AND (Item_Date >= ‘20070306’)
AND (Item_Date < ‘20070308’)
GROUP BY Item_Id
ORDER BY Item_Id
) a KH
]]>