Group results into one row

[Cpreston]

Hi

I have a query that I want to display all results into one row

The query currently returns the following



But I want it to display the figures in one row for Status as Free and one row as Status Allocated with the Total being the total of all the row. I have tried group by and other suggestions but cannot seem to get the result I am after. Here is my query, could someone advise please. Thanks in advance.

SELECT        ProductCode, CASE WHEN PackStatus = 10 THEN 'Free' WHEN PackStatus = 11 THEN 'Allocated' ELSE 'Received/Reconciled' END AS Status, CASE WHEN Length = 2.4 THEN Packs ELSE NULL END AS [2.4],
                         CASE WHEN Length = 3.0 THEN Packs ELSE NULL END AS [3.0], CASE WHEN Length = 3.6 THEN Packs ELSE NULL END AS [3.6], CASE WHEN Length = 4.2 THEN Packs ELSE NULL END AS [4.2],
                         CASE WHEN Length = 4.8 THEN Packs ELSE NULL END AS [4.8], CASE WHEN Length = 5.4 THEN Packs ELSE NULL END AS [5.4], CASE WHEN Length = 6.0 THEN Packs ELSE NULL END AS [6.0],
                         Packs AS Total
FROM            dbo.[148-vwproductfreefeedCP]
GROUP BY Packs, PackStatus, Length, ProductCode

 

[Andrzejek]

How come the second record's Total is 5, even though all Fields (2.4 to 6.0) are NULLs?
The same goes for 5th, 6th, and other records.

 

[gmmastros]

Try this...

SELECT    ProductCode, 
        CASE WHEN PackStatus = 10 THEN 'Free' 
             WHEN PackStatus = 11 THEN 'Allocated' 
             ELSE 'Received/Reconciled' END AS Status, 
        Min(CASE WHEN Length = 2.4 THEN Packs ELSE NULL END) AS [2.4],
        Min(CASE WHEN Length = 3.0 THEN Packs ELSE NULL END) AS [3.0], 
        Min(CASE WHEN Length = 3.6 THEN Packs ELSE NULL END) AS [3.6], 
        Min(CASE WHEN Length = 4.2 THEN Packs ELSE NULL END) AS [4.2],
        Min(CASE WHEN Length = 4.8 THEN Packs ELSE NULL END) AS [4.8], 
        Min(CASE WHEN Length = 5.4 THEN Packs ELSE NULL END) AS [5.4], 
        Min(CASE WHEN Length = 6.0 THEN Packs ELSE NULL END) AS [6.0],
        Packs AS Total
FROM    dbo.[148-vwproductfreefeedCP]
GROUP BY ProductCode, 
        CASE WHEN PackStatus = 10 THEN 'Free' 
             WHEN PackStatus = 11 THEN 'Allocated' 
             ELSE 'Received/Reconciled' END,
        Packs

 

[Andrzejek]

How about simply:

SELECT ProductCode, Status, SUM([2.4]) As [2.4], SUM([3.0]) As [3.0], ..., SUM(Total) As Total
FROM dbo.[148-vwproductfreefeedCP]
GROUP BY ProductCode, Status

 

[Chris Miller]

I agree with Andy, just COUNT([2.4]) etc. and I think the total would need another idea, too.


Edit: ...reconsidering... As the Length is used to define columns it would be a case for pivoting, wouldn't it?

 

[Andrzejek]

I didn't say COUNT([2.4]), I said SUM([2.4]) As [2.4]
And as far as Total, I would use a calculated/computed field so it is adding those other fields 'auto-magically'

 

[Chris Miller]

Well, Andy, the data is pivoted on Lengths and what's totalled for each length is a number of [Packs], I now think neither SUM([2.4]) nor COUNT[2.4] will work, these are generated fields that don't exist in the dbo.[148-vwproductfreefeedCP] view.

 

[Andrzejek]

I see this post is just a part of a bigger discussion with Cpreston's issues. And without knowing the previous info I just 'shot in the dark' based on what's available here only

 

[Chris Miller]

Cpreston,

can you show me (us) some sample rows of the view?

Select Top 10 * From dbo.[148-vwproductfreefeedCP] Order By ProductCode

 

[Andrzejek]

Here is what I've tried:
Created a table (Andy):

and based on this data, I have this SQL:

SELECT ProductCode
      ,Status
      ,SUM([2.4]) AS [2.4]
      ,SUM([3.0]) AS [3.0]
      ,SUM([3.6]) AS [3.6]
      ,SUM([4.2]) AS [4.2]
      ,SUM([4.8]) AS [4.8]
      ,SUM([5.4]) AS [5.4]
      ,SUM([6.0]) AS [6.0]
      ,SUM(Total) AS Total
  FROM Andy
  GROUP BY ProductCode, Status

and the outcome looks like this:


I am sure I am missing something here, but... this is what works for me

 

[Chris Miller]

That would work when you start from the wrong result. So, when "Andy" was a derived query table expression or with a common table expression (CTE).
I'd rather solve this based on the original dbo.[148-vwproductfreefeedCP] data. And even though Cpreston's result is not quite what's wanted, you can see he sums Packs into the different length columns, which is exaclty what PIVOT can do.

Something along the lines of (unfinished sketch of a pivot query):

;WITH ProductPacks AS
(
    SELECT ProductCode
          ,Length
          ,Packs
    FROM dbo.[148-vwproductfreefeedCP]
)
    SELECT  PVT.ProductCode
           ,PVT.["2.4"]
           ,PVT.["3.0"]
           ,PVT.["3.6"] 
    FROM ProductPacks
    PIVOT (
           SUM(Packs)
           FOR Length
           IN ([2.4],[3.0],[3.6])
          ) AS PVT;

Excuse the fact it doesn't yet have anything about the Status and no Total column, it'll likely fail on Length being numeric, but I know it's possible to get it going, as it's not unusual to pivot by the year, for example.

I also know the column list could even be derived dynamically from all distinct length values, so you'd get a new column when a new length is introduced. Anyway, I'd only get more concrete knowing what's available to start with and not base a solution on the partial but wrong try.