Group by Name 1

[briesen]

I'm trying to group some data together in which I want to separate groups to be considered one. Below is a sample query, but basically I want LargeNail and SmallNail to be considered the group 'Nail'. The issue is that it just lists 'Nail' twice, with all the numbers separated. I want the two to add together. Is this possible?

SELECT  CASE WHEN (vtr.Type='LargeNail' OR vtr.Type='SmallNail') THEN 'Nail' ELSE vtr.Type END,

               'Weight'=SUM(vtr.Weight),

        'Total Cost'=SUM(vtr.TotalARAmt),

        'Count'=COUNT(vtr.[Order Number])

FROM    DB.dbo.vtr

        WHERE   vtr."CustomerShortName"='CUST'

        AND (vtr."Billing Date">= CONVERT(DATETIME, '2010-09-01 00:00:00', 102))

        AND (vtr."Billing Date"< DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0))

        AND vtr."Routed"=1
 
        AND (vtr.Type='LargeNail' OR vtr.Type='SmallNail' OR vtr.Type='Screw') 

GROUP by vtr.Type

 

[gmmastros]

Try changing your group by like this:

SELECT  CASE WHEN (vtr.Type='LargeNail' OR vtr.Type='SmallNail') THEN 'Nail' ELSE vtr.Type END,
               'Weight'=SUM(vtr.Weight),
        'Total Cost'=SUM(vtr.TotalARAmt),
        'Count'=COUNT(vtr.[Order Number])
FROM    DB.dbo.vtr
        WHERE   vtr."CustomerShortName"='CUST'
        AND (vtr."Billing Date">= CONVERT(DATETIME, '2010-09-01 00:00:00', 102))
        AND (vtr."Billing Date"< DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0))
        AND vtr."Routed"=1
        AND (vtr.Type='LargeNail' OR vtr.Type='SmallNail' OR vtr.Type='Screw') 
[!]GROUP by CASE WHEN (vtr.Type='LargeNail' OR vtr.Type='SmallNail') THEN 'Nail' ELSE vtr.Type END[/!]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom