CODEWITHSUNDEEP
sqlsql-servert-sql
M H
M H

Reputation: 2182

SQL Server Record Grouping

This is my query:

Select distinct 'R' + cast(f.ProjID as VarChar(20)) + '_' + cast(WS.Name as varchar(25)) + '_' + cast(f.catID as varchar(25)) + '_' + cast(spl.CompID as Varchar(25)) as DocNumber, 
    f.Quantity, 
    spl.PartName, 
    ct.Name as Section
from equipment.dbo.fixture f 
Join Equipment.dbo.ScoutPartsList spl on f.PartID = spl.ID 
Join Category ct on f.CatID = ct.id 
Join Store S ON F.StoreID= S.ID 
Join Store WS ON S.WarehouseID = WS.ID 
Where f.ProjID=6715 and f.Catid=4715

This is my Result:

   DocNumber         Quantity         PartName                    Section
R6715_AZ A_4715_1831    0   Trion 20" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    1   Trion 22" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    2   Trion 22" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    4   Trion 22" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    5   Trion 22" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    8   Trion 22" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    10  Trion 22" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    11  Trion 22" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    12  Trion 22" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    13  Trion 22" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    16  Trion 22" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    20  Trion 22" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    21  Trion 22" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    22  Trion 22" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    23  Trion 22" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    24  Trion 22" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    25  Trion 22" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    30  Trion 22" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    31  Trion 22" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    35  Trion 22" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    38  Trion 22" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    45  Trion 22" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    50  Trion 22" Pusher (Frozen Potato)    FROZEN POTATO

I would like to Sum the quantity colummn and group by the doc number. So if there were two types of parts for one doc number I would only have two results (One for each part with the sum of thos parts) instead of bunches.

Expected Result (quantities not actually correct):

R6715_AZ A_4715_1831    124 Trion 20" Pusher (Frozen Potato)    FROZEN POTATO
R6715_AZ A_4715_1831    364 Trion 22" Pusher (Frozen Potato)    FROZEN POTATO

Any ideas?

Upvotes: 0

Views: 49

Answers (3)

M H
M H

Reputation: 2182

Resolved.

Group By alias was wrong on my end.

Select distinct 'R' + cast(f.ProjID as VarChar(20)) + '_' + cast(WS.Name as varchar(25)) + '_' + cast(f.catID as varchar(25)) + '_' + cast(spl.CompID as Varchar(25)) as DocNumber, sum(f.Quantity), spl.PartName, ct.Name as Section
    from equipment.dbo.fixture f 
    Join Equipment.dbo.ScoutPartsList spl on f.PartID = spl.ID 
    Join Category ct on f.CatID = ct.id 
    Join Store S ON F.StoreID= S.ID 
    Join Store WS ON S.WarehouseID = WS.ID 
    Where f.ProjID=6715 and f.Catid=4715
    Group By f.ProjID, ws.Name, f.catID, spl.CompId, spl.PartName, ct.Name

Upvotes: 0

Josh Part
Josh Part

Reputation: 2164

SELECT DocNumber, SUM(Quantity) Quantity, PartName, Section
FROM (
Select distinct 'R' + cast(f.ProjID as VarChar(20)) + '_' + cast(WS.Name as varchar(25)) + '_' +     cast(f.catID as varchar(25)) + '_' + cast(spl.CompID as Varchar(25)) as DocNumber, 
    Quantity, 
    spl.PartName, 
    ct.Name as Section
from equipment.dbo.fixture f 
Join Equipment.dbo.ScoutPartsList spl on f.PartID = spl.ID 
Join Category ct on f.CatID = ct.id 
Join Store S ON F.StoreID= S.ID 
Join Store WS ON S.WarehouseID = WS.ID 
Where f.ProjID=6715 and f.Catid=4715
)
Group by DocNumber, PartName, Section

SQL Server tells you that some columns are not contained in the group clause because you need to include in the Group By clause all columns that are not grouped. In this case you want to group by all columns but quantity, so that's what you need to tell to SQL Server...

Upvotes: 0

dario
dario

Reputation: 5259

I guess you want something like this:

WITH DATA
AS
(
    SELECT DISTINCT 'R' + CAST(f.ProjID AS VARCHAR(20)) + '_' + CAST(WS.Name AS VARCHAR(25)) + '_' + CAST(f.catID AS VARCHAR(25)) + '_' + CAST(spl.CompID AS VARCHAR(25)) AS DocNumber
         ,f.Quantity
         ,spl.PartName
         ,ct.Name as Section
    FROM equipment.dbo.fixture AS F
    JOIN Equipment.dbo.ScoutPartsList spl 
        ON f.PartID = spl.ID 
    JOIN Category ct 
        ON f.CatID = ct.id 
    JOIN Store S 
        ON F.StoreID= S.ID 
    JOIN Store WS 
        ON S.WarehouseID = WS.ID 
    WHERE f.ProjID=6715 AND f.Catid=4715
)
SELECT DocNumber
      ,SUM(Quantity) AS Quantity
      ,PartName
      ,MAX(Section) AS Section
FROM DATA
GROUP BY DocNumber, PartName;

Upvotes: 1

Related Questions