Group results into one row 3

[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.

 

[Cpreston]

Wow thanks for the replies I will give them a try and get back, but huge thanks for the replies.

 

[Cpreston]

Hi

Ok close but I encountered another issue when looking at the results

The 148-vwproductfreeCP View feeds off the below query which is called 148-vwproductfreefeedCP

SELECT        dbo.Product.ProductCode, dbo.ProductPackDetail.Length, COUNT(dbo.ProductPack.PackID) AS Packs, dbo.Product.udfWebDescription, dbo.ProductPack.PackStatus, dbo.Product.Deleted
FROM            dbo.ProductPackDetail WITH (NOLOCK) RIGHT OUTER JOIN
                         dbo.ProductPack WITH (NOLOCK) ON dbo.ProductPackDetail.PackID = dbo.ProductPack.PackID RIGHT OUTER JOIN
                         dbo.Product WITH (NOLOCK) ON dbo.ProductPack.ProductID = dbo.Product.ProductID
WHERE        (dbo.ProductPack.PackStatus IN (10, 11))
GROUP BY dbo.ProductPack.Deleted, dbo.ProductPack.BranchID, dbo.ProductPackDetail.Length, dbo.Product.ProductCode, dbo.Product.udfWebDescription, dbo.ProductPack.PackStatus, dbo.Product.Deleted
HAVING        (dbo.ProductPack.Deleted = 0) AND (dbo.ProductPack.BranchID = 1) AND (dbo.Product.ProductCode = 'SSR047125DA') AND (dbo.Product.Deleted = 0)

If I use the suggestion given by Andrzek I have for the 148-vwproductfreeCP

SELECT ProductCode
      ,Status
      ,SUM([2.4]) AS [2.4]
      ,SUM([2.7]) AS [2.7]
      ,SUM([3.0]) AS [3.0]
      ,SUM([3.3]) AS [3.3]
      ,SUM([3.6]) AS [3.6]
      ,SUM([3.9]) AS [3.9]
      ,SUM([4.2]) AS [4.2]
      ,SUM([4.5]) AS [4.5]
      ,SUM([4.8]) AS [4.8]
      ,SUM([5.1]) AS [5.1]
      ,SUM([5.4]) AS [5.4]
      ,SUM([6.0]) AS [6.0]
      ,SUM(Total) AS Total
  FROM [148-vwproductfreeCP]
  GROUP BY ProductCode, Status

Which gives me the result of


Which is great. However, I have noticed if a Product Code (under the product codes there are packs) as more than one pack length then it counts them twice. So for Free is as a 3.0 and 3.3 length, so it is giving me 2 for 3.0 and 5 for 3.3 where the figure should be 1 for 3.0 and 4 for 3.3. If it only as one pack the totals are spot on. Any ideas on how to do this , I tired Distinct but that did not work. Thanks again

​

 

[Andrzejek]

My suggestion will not work if your requirements keep changing.
From your original post you had Fields:
ProductCode Status 2.4 3.0 3.6 4.2 4.8 5.4 6.0 Total
now you have:
ProductCode Status 2.4 2.7 3.0 3.3 3.6 3.9 4.2 4.5 4.8 5.1 5.4 6.0 Total
Unless you build your Select 'on-the-fly' (in code), you would need to change the Select statement every time.
And that's not the way to program...

 

[Chris Miller]

I just say dynamic pivoting, but CPreston, it won't help to see your view query, it's partly helpful, explains a bit about the origin of Packs, but not what PackIDs are and meanings of many other things. I asked to see some data to get a better picture.

I think you could far easier get the sums you need when your view wouldn't do partial summing of the packs, for example.

It's also puzling from the view query you posted, why you have these numeric column names, they are only generated by your not quote working query and so you'd need to combine that with Andys to get to your result, and then miraculously get further lengths, it's all a big puzzle.

 

[Cpreston]

Hi

Sorry to have caused any confusion, there are more lengths than I first understood of the situation, however even if I break it down to the original post for this

,SUM([3.0]) AS [3.0]

I still get a result of 2 and not 1. I was asked for more information which is what I tried to do. Not sure where to go from here it is beyond my SQL skills.

Thanks for the reply's anyway.

 

[Chris Miller]

CPreston, I asked you to run a query.

 

[Cpreston]

Hi Chriss

What query do you want me to run please, sorry I am getting slightly confused with all the replies. I will run it immediately. Thanks

 

[Cpreston]

Chris if you are referring to this Select Top 10 * From dbo.[148-vwproductfreefeedCP] Order By ProductCode

I get the result

 

[Chris Miller]

Thanks.

I don't know how to let PIVOT create a total column, but otherwise this will give you every length as a column and you can do totals as an aftermath:

Declare @Lengths varchar(100);
Declare @sql varchar(8000);

SELECT @Lengths = STRING_AGG(Length,'],[') From (Select Distinct Length From dbo.[148-vwproductfreefeedCP]) lengths;

Set @sql = 'SELECT PVT.*,
    CASE WHEN PackStatus = 10 THEN ''Free'' WHEN PackStatus = 11 THEN ''Allocated'' ELSE ''Received/Reconciled'' END AS Status
    FROM dbo.[148-vwproductfreefeedCP]
    PIVOT (
           SUM(Packs)
           FOR Length IN (['+@Lengths+'])
          ) AS PVT';
          
execute(@sql)