Grouping SQL Database by First Character

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Grouping SQL Database by First Character

Author

Topic

bielen
Yak Posting Veteran

97 Posts

Posted - 2008-03-12 : 16:46:29

I have the following set of results which obtain from my query. The data is inconsisten so I want to be able to group the status by the first character. For example, 1-High and 1-Urgent would be grouped into the same group:

Date Status Product TotalCount
2008.03.06 (None) ProductName 1409
2008.03.12 (None) ProductName 1409
2008.03.06 0-Urgent ProductName 2
2008.03.12 0-Urgent ProductName 2
2008.03.06 1-High ProductName 7
2008.03.12 1-High ProductName 7
2008.03.06 1-Urgent ProductName 176
2008.03.12 1-Urgent ProductName 185
2008.03.06 2-High ProductName 577
2008.03.12 2-High ProductName 573
2008.03.06 2-Medium ProductName 30
2008.03.12 2-Medium ProductName 30
2008.03.06 3-Low ProductName 78
2008.03.12 3-Low ProductName 78
2008.03.06 3-Medium ProductName 340
2008.03.12 3-Medium ProductName 343
2008.03.06 4- Medium ProductName 1
2008.03.12 4- Medium ProductName 1
2008.03.06 4-Low ProductName 91
2008.03.12 4-Low ProductName 86
2008.03.06 5-Low ProductName 2
2008.03.12 5-Low ProductName 2

My results for 4 for example (last four rows) should be:

2008.03.06 4 ProductName 93
2008.03.12 4 ProductName 88

I tried LEFT(Status,1). Only the field is trimmed, but the records do not total.

Any info is appreciated.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-12 : 16:52:54

show us your sql

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

bielen
Yak Posting Veteran

97 Posts

Posted - 2008-03-13 : 10:15:12

Here's the SQL:

SELECT Test_Data.SnapShotWeek, LEFT(Test_Data.Severity, 1) AS Expr1, Test_Product_Groups.groupName, COUNT(*) AS Total
FROM Test_Data LEFT OUTER JOIN Test_Product_Releases ON Test_Product_Releases.releaseNumber = Test_Data.Release LEFT OUTER JOIN Test_Product_Groups ON Test_Data.Product = Test_Product_Groups.productName
WHERE (Test_Data.RptStatus <> 'Closed') AND (Test_Product_Releases.releaseNumber IS NOT NULL) AND (Test_Product_Groups.groupName = "Product A")
GROUP BY Test_Data.Severity, Test_Data.SnapShotWeek, Test_Product_Groups.groupName
ORDER BY Test_Data.Severity, Test_Data.SnapShotWeek

I can filter on the value, but it does not group.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-03-13 : 10:19:44

GROUPing must be done the same way as the SELECT

i.e GROUP BY Test_Data.SnapShotWeek, LEFT(Test_Data.Severity, 1) AS Expr1, Test_Product_Groups.groupName, COUNT(*) AS Total

bielen
Yak Posting Veteran

97 Posts

Posted - 2008-03-13 : 11:20:20

Thank you, but I tried that before I posted my original message:

SELECT Test_Data.SnapShotWeek, LEFT(Test_Data.Severity, 1) AS Severity, Test_Product_Groups.groupName
FROM Test_Data LEFT OUTER JOIN
Test_Product_Releases ON Test_Product_Releases.releaseNumber = Test_Data.Release LEFT OUTER JOIN
Test_Product_Groups ON Test_Data. Product = Test_Product_Groups.productName
WHERE (Test_Data.RptStatus <> 'Closed') AND (Test_Product_Groups.groupName = 'Facets') AND (Test_Product_Releases.releaseNumber IS NOT NULL)
GROUP BY Test_Data.SnapShotWeek, LEFT(Test_Data.Severity, 1) AS Severity, Test_Product_Groups.groupName
ORDER BY Test_Data.Severity, Test_Data.SnapShotWeek

As soon as add "AS Severity" to the group by statement, I receive a syntax error. If I remove the AS statement, the records appear as originally outlined, but do not group. Any other ideas?

Thank for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-13 : 11:29:26

Yu dont require alias in GROUP BY just giving LEFT(Test_Data.Severity, 1) should do trick for you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources