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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
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 (
SELECT LEFT(FileDescription,4) AS [ID],
ROW_NUMBER()
OVER (
PARTITION BY LEFT (FileDescription, 4)
ORDER BY LEFT (FileDescription, 4)
) AS SeqID,
FileDescription,
Points
FROM <<Table Name>>
)
SELECT cte.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