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) A socialist is someone who will give you the shirt off *someone else's* back.
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