April 26, 2012 at 7:17 am
I have a table which I am trying to sum. Simple enough.
Field - Project
Field - Item Code
Field - Item Description
Sum(Hours)
The problem is that the Item Description doesn't always match and occasionally an Item won't show in all projects.
Solution 1) Include description in the Group By. However the problem here is that the descriptions don't always match so it doesn't fully/correctly sum.
Solution 2) Don't include description in the Group By. I would really like to have descriptions
Solution 3) Create an additional Join to the same table, using a single project. Then the description from there. This actually works and I like it except sometimes the Item Code doesn't exist in the description of the project I choose. Then I get Null's.
How might I use the descriptions from the project as in Solution 3 and if I see a null then get the description from next project in line?
Thank you,
April 26, 2012 at 7:37 am
April 26, 2012 at 7:44 am
Ken at work (4/26/2012)
I have a table which I am trying to sum. Simple enough.Field - Project
Field - Item Code
Field - Item Description
Sum(Hours)
The problem is that the Item Description doesn't always match and occasionally an Item won't show in all projects.
Solution 1) Include description in the Group By. However the problem here is that the descriptions don't always match so it doesn't fully/correctly sum.
Solution 2) Don't include description in the Group By. I would really like to have descriptions
Solution 3) Create an additional Join to the same table, using a single project. Then the description from there. This actually works and I like it except sometimes the Item Code doesn't exist in the description of the project I choose. Then I get Null's.
How might I use the descriptions from the project as in Solution 3 and if I see a null then get the description from next project in line?
Thank you,
The short answer is no, every field must either be an aggregate or in the group by.
The first question is WHY does the item description NOT match the item ID every time... it smacks of bad database design and if an item has more than one description, which description would you like. You could use a MIN() or MAX() on the description.
The alternative is to create two Common table expressions (CTE) and join them together
WITH CTE1 AS
(
SELECT Project,ItemCode,SUM(qty) as 'TotQty' FROM myTable GROUP BY Project,ItemCode
),
CTE2 AS
(
SELECT DISTINCT Project,ItemCode,ItemDesc from myTable
)
Select CTE1.*,CTE2.ItemDesc FROM CTE1 JOIN CTE2 ON CTE1.Project = CTE2.Project AND CTE1.ItemCode = CTE2.ItemCode
This select will give you the (full) item count twice if there are two descriptions for the item.
April 26, 2012 at 7:52 am
That is what I was thinking. I was trying to figure a Case method but couldn't come up with one.
Actually the DB isn't mine (DB9 using a Linked Server) and has is a ton more data, it is Main Project and Sub Project. Each has separate Items and Item Descriptions. While the Items and Descriptions often match there is nothing to prevent the Project Manger from creating an item in the SubProject which doesn't exist in the Main Project or even using a different description. Sigh...
Of course when the PM wants to see a consolidated report of all items on a Project (regardless of main or sub) I run into this situation of the descriptions either not matching or not existing in the Main Project.
Thank you,
April 26, 2012 at 8:28 am
Have you considered WITH ROLLUP and WITH CUBE on the Group by statement.
these will provide a summary at each level.
e.g. current results set looks like
Proj Item Desc Qty
001 AAA Desc1 22
001 AAA Desc2 33
001 BBB Desc3 44
WITH ROLLUP will show
Proj Item Desc Qty
001 AAA Desc1 22
001 AAA Desc2 33
001 BBB Desc3 44
001 AAA NULL 55 -- being rolled up values of all 001/AAA
001 BBB NULL 44 -- being rolled up values of all 001/BBB
001 NULL NULL 99 -- being rolled up values of all 001
NULL NULL NULL 99 -- being rolled up value of everything
But be careful with rollup when using anything other than SUM - you get some odd results with MIN,MAX and AVG!
April 26, 2012 at 8:34 am
I have never used Rollup,
I need to check that out.
April 26, 2012 at 8:16 pm
This solution will sum the Qty by Proj, Item and include the first non-NULL description in the results.
DECLARE @t TABLE
(Proj VARCHAR(3), Item VARCHAR(3), [Desc] VARCHAR(20), Qty INT)
INSERT INTO @t
SELECT '001','AAA','Desc1',22
UNION ALL SELECT '001','AAA','Desc2',33
UNION ALL SELECT '001','BBB','Desc3',44
UNION ALL SELECT '001','AAA',NULL,55
UNION ALL SELECT '001','BBB',NULL,44
;WITH a AS (
SELECT Proj, Item, [Desc], Qty
,ROW_NUMBER() OVER (PARTITION BY Proj, Item ORDER BY Proj, Item) As rk
FROM @t
WHERE [Desc] IS NOT NULL)
SELECT Proj, Item
,(SELECT TOP 1 [Desc] FROM a WHERE a.Item = t1.Item and rk=1) As [Desc]
,SUM(Qty) As Qty
FROM @t t1
GROUP BY Proj, Item
It is not guaranteed to pull the description from the "next project in line," whatever that means. If you clarify it I may be able to help you further, but it should just be a matter of manipulating the PARTITION/ORDER BY columns (or possibly the WHERE on the subquery) to get exactly what you want.
You would need to expand the sample data to include additional projects (use the consumable form in my sample SQL above) and exactly the expected results you want to see.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 26, 2012 at 8:37 pm
This version will probably perform a little better but it doesn't offer quite as fine a degree of control over specifically which description appears.
;WITH a AS (
SELECT Proj, Item, [Desc]
,ROW_NUMBER() OVER (PARTITION BY Proj, Item ORDER BY Proj, Item, [Desc] DESC) As rk
, SUM(Qty) OVER (PARTITION BY Proj, Item) As Qty
FROM @t)
SELECT Proj, Item, [Desc], Qty
FROM a
WHERE [Desc] IS NOT NULL and rk=1
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 29, 2012 at 5:43 pm
Is the description all that important if it's not FKd? Probably not. So just do a MAX() on the description and call it a day. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 1, 2012 at 6:52 am
I think I'm going to have to do some reading.
The preference is to use the first rather than the longest, or first/last in the alphabet.
But honestly, I'm new at this so I need to play with this to even understand how it works.
I'm going to give it a whirl though.
I appreciate it.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply