CODEWITHSUNDEEP
sqlsql-server
Dave
Dave

Reputation: 253

Want one row of data but group by does not appear to be having that affect

I want to just return one row of data with the sum of data and also group by production plan item id ideally what i want is a row showing me that field and the sum qty value so it should read

ProductionPlanItemId    QtyCompleted
5865406                 3  

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT [CompletedPrintedId]
      ,[UserName]
      ,[ProductionPlanId]
      ,[QtyCompleted]
      ,[SubAssembledQty]
      ,[QtyRequired]
      ,[ProductionPlanItemID]
      ,[SOPOrderReturnLineId]
FROM [CompletedPrinted]
where DocumentNo='0000027084'  and ProductionPlanItemID='5865406'
GROUP BY 
      [ProductionPlanItemID]
      ,[UserName]
      ,[ProductionPlanId]
      ,[QtyCompleted]
      ,[SubAssembledQty]
      ,[QtyRequired]
      , [CompletedPrintedId]
      ,[SOPOrderReturnLineId]

The Colum schema is

CREATE TABLE [dbo].[CompletedPrinted](
    [CompletedPrintedId] [bigint] NOT NULL,
    [UserName] [nvarchar](66) NOT NULL DEFAULT (''),
    [StartDateTIme] [datetime] NULL,
    [EndDateTime] [datetime] NULL,
    [ProductionPlanId] [bigint] NOT NULL DEFAULT ((0)),
    [SopLineItemId] [nvarchar](64) NOT NULL DEFAULT (''),
    [Detail] [nvarchar](1002) NOT NULL DEFAULT (''),
    [isActive] [bit] NOT NULL DEFAULT ((0)),
    [DocumentNo] [nvarchar](102) NOT NULL DEFAULT (''),
    [StockCode] [nvarchar](32) NOT NULL DEFAULT (''),
    [StockDescription] [text] NOT NULL DEFAULT (''),
    [QtyCompleted] [bigint] NOT NULL DEFAULT ((0)),
    [SubAssembledQty] [bigint] NOT NULL DEFAULT ((0)),
    [QtyRequired] [bigint] NOT NULL DEFAULT ((0)),
    [ProductionPlanItemID] [bigint] NOT NULL DEFAULT ((0)),
    [SOPOrderReturnLineId] [bigint] NOT NULL DEFAULT ((0))
)

I have created a sql fiddle below with the sample data.

http://sqlfiddle.com/#!18/8927c/2

enter image description here

Edit 2 Sorry I should have stated I need the other columns to be there.

Upvotes: 0

Views: 30

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use row_number() if you need one row per ProductionPlanId

select * from (SELECT [CompletedPrintedId]
      ,[UserName]
      ,[ProductionPlanId]
      ,[QtyCompleted]
      ,[SubAssembledQty]
      ,[QtyRequired]
      ,[ProductionPlanItemID]
      ,[SOPOrderReturnLineId]
      ,row_number() over(partition by ProductionPlanId
                          order by QtyCompleted) rn
  FROM [CompletedPrinted]
  where DocumentNo='0000027084'  and ProductionPlanItemID='5865406'
) a where a.rn=1

demo link

but it seems you just need sum()

select    
      [ProductionPlanId]
      ,sum(QtyCompleted) 

  FROM [CompletedPrinted]
  where DocumentNo='0000027084'  and ProductionPlanItemID='5865406'
  group by ProductionPlanId

output

 ProductionPlanId   QtyCompleted
  5865405             3

as you need all column as well so try below

/****** Script for SelectTopNRows command from SSMS ******/

 with cte as
  ( select    
     c.ProductionPlanId
      ,sum(QtyCompleted)  as QtyCompleted

  FROM [CompletedPrinted] c
  where DocumentNo='0000027084'  and ProductionPlanItemID='5865406'
  group by ProductionPlanId
    ) , cte2 as
    (select cte.QtyCompleted as MQtyCompleted,c2.*,
       row_number()over(partition by c2.ProductionPlanId order by cte.QtyCompleted) rn
       from
    cte join CompletedPrinted c2
    on cte.ProductionPlanId=c2.ProductionPlanId
     ) select * from cte2 where rn=1

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Exclude all columns except ProductionPlanId in GROUP BY clause & do aggregation : 

SELECT [ProductionPlanId], SUM([QtyCompleted])
FROM [CompletedPrinted]
WHERE DocumentNo = '0000027084' AND ProductionPlanItemID='5865406'
GROUP BY [ProductionPlanId];

Upvotes: 0

Related Questions