April 4, 2013 at 6:16 am
I have the following data:
File Description Points
1001- Industrial 100
1001-001 Barker 200
1001-002 Curry 175
1002- Buildings 375
1003- HVAC 225
1003-001 Wing School 125
I am grouping on the substring (1,4) of File but need the description that goes with first or min File only. I need the data from all records. The results should look like this:
1001- Industrial 475
1002- Buildings 375
1003- HVAC 350
April 4, 2013 at 7:15 am
Assuming the space after hyphen is intentional to indicate heading then
;WITH cte (ID,Heading,FileDescription,Points)
AS (
SELECT LEFT(FileDescription,4) AS [ID],SUBSTRING(FileDescription,6,1) AS [Heading],FileDescription,Points
FROM @a
)
SELECT cte.FileDescription,SUM(p.Points) AS [Points]
FROM cte
JOIN cte p ON p.ID = cte.ID
WHERE cte.Heading = ' '
GROUP BY cte.FileDescription
Far away is close at hand in the images of elsewhere.
Anon.
April 4, 2013 at 11:00 am
SELECT
LEFT(File, 4) AS File,
MAX(CASE WHEN SUBSTRING(File, 6, 1) = '' THEN Description ELSE '' END) AS Description,
SUM(Points) AS Points
FROM dbo.tablename
GROUP BY LEFT(File, 4)
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".
April 4, 2013 at 12:36 pm
Thanks - the Max with case worked - just had to modify for null values.
April 4, 2013 at 2:06 pm
Here's another version which uses ROW_NUMBER()...
;WITH cte (ID, SeqID, FileDescription, Points)
AS (
SELECTLEFT(FileDescription,4) AS [ID],
ROW_NUMBER()
OVER (
PARTITION BY LEFT (FileDescription, 4)
ORDER BY LEFT (FileDescription, 4)
) AS SeqID,
FileDescription,
Points
FROM <<Table Name>>
)
SELECTcte.FileDescription, SUM(p.Points) AS [Points]
FROM cte
JOIN cte p ON p.ID = cte.ID
WHERE cte.SeqID = 1
GROUP BY cte.FileDescription
- Rex
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy