July 20, 2012 at 2:18 pm
I can run the query just fine without the line above GROUP BY, but not with it. What am I doing wrong? As you can see, I am fairly new at SQL. Thank you.
SELECT dbo.IIRPS_HPOO_LPAR.LPAR, SUM(MaxHeapSize) AS SumMaxHeapSize, COUNT(dbo.IIRPS_HPOO_IHS.LPAR) AS IHSInstances
FROM dbo.IIRPS_HPOO_IHS, dbo.IIRPS_HPOO_LPAR, dbo.IIRPS_HPOO_JVM
WHERE dbo.IIRPS_HPOO_JVM.LPAR = dbo.IIRPS_HPOO_LPAR.LPAR AND dbo.IIRPS_HPOO_LPAR.LPAR = dbo.IIRPS_HPOO_IHS.LPAR
AND (SUM(MaxHeapSize) * 1.6) + (1024 * COUNT(dbo.IIRPS_HPOO_IHS.LPAR)) = OnlineMemory
GROUP BY dbo.IIRPS_HPOO_IHS.LPAR
July 20, 2012 at 2:26 pm
You should be using HAVING in this case. http://msdn.microsoft.com/en-us/library/ms180199.aspx
SELECT dbo.IIRPS_HPOO_LPAR.LPAR, SUM(MaxHeapSize) AS SumMaxHeapSize, COUNT(dbo.IIRPS_HPOO_IHS.LPAR) AS IHSInstances
FROM dbo.IIRPS_HPOO_IHS
inner join dbo.IIRPS_HPOO_LPAR on dbo.IIRPS_HPOO_LPAR.LPAR = dbo.IIRPS_HPOO_IHS.LPAR
inner join dbo.IIRPS_HPOO_JVM on dbo.IIRPS_HPOO_JVM.LPAR = dbo.IIRPS_HPOO_LPAR.LPAR
GROUP BY dbo.IIRPS_HPOO_IHS.LPAR
having (SUM(MaxHeapSize) * 1.6) + (1024 * COUNT(dbo.IIRPS_HPOO_IHS.LPAR)) = OnlineMemory
Also, you were using the old style of join syntax. I changed this to use inner joins instead. It doesn't actually provide any benefit to the query but it is a lot easier to read. The other disadvantage to the comma separated list is that it is fairly easy to create a cross join if you forget to add one of the equality checks in the where clause.
_______________________________________________________________
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/
July 20, 2012 at 2:29 pm
Thank you, but I am now getting this error:
Column 'dbo.IIRPS_HPOO_LPAR.OnlineMemory' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
July 20, 2012 at 2:31 pm
Oops...
SELECT dbo.IIRPS_HPOO_LPAR.LPAR, SUM(MaxHeapSize) AS SumMaxHeapSize, COUNT(dbo.IIRPS_HPOO_IHS.LPAR) AS IHSInstances,
(SUM(MaxHeapSize) * 1.6) + (1024 * COUNT(dbo.IIRPS_HPOO_IHS.LPAR)) as OnlineMemoryCheck
FROM dbo.IIRPS_HPOO_IHS
inner join dbo.IIRPS_HPOO_LPAR on dbo.IIRPS_HPOO_LPAR.LPAR = dbo.IIRPS_HPOO_IHS.LPAR
inner join dbo.IIRPS_HPOO_JVM on dbo.IIRPS_HPOO_JVM.LPAR = dbo.IIRPS_HPOO_LPAR.LPAR
GROUP BY dbo.IIRPS_HPOO_IHS.LPAR, Memory
having (SUM(MaxHeapSize) * 1.6) + (1024 * COUNT(dbo.IIRPS_HPOO_IHS.LPAR)) = OnlineMemory
_______________________________________________________________
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/
July 20, 2012 at 2:37 pm
rupes0610 (7/20/2012)
Thank you, but I am now getting this error:Column 'dbo.IIRPS_HPOO_LPAR.OnlineMemory' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
If it's the same for every row/group, you can use "MAX(OnlineMemory)" in the HAVING clause instead of just "OnlineMemory"
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply