Group By problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group By problem

Author

Topic

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2011-09-01 : 20:47:22

Dear all,

I have a set of records here:

Product_ID Product_Group Status
NY11-1 NY11 A
NY11-2 NY11 A
NY12-1 NY12 I
NY12-2 NY12 A
NY13-1 NY13 A

The results that I expect is as below:
Product_Group Product_ID Status
NY11 NY11-1 A
NY11 NY11-2 A
NY12 NY12-2 A
NY13 NY13-1 A

I tried to group by only when the product exist more than one time and there are different status within same product group. How can I do that? Because i tried to use group by but it'll group by all no matter there are different status within same product group or don't have.

ben_53
Yak Posting Veteran

67 Posts

Posted - 2011-09-01 : 21:31:17

"I tried to group by only when the product exist more than one time and there are different status within same product group"
but in your expected result set you have same status within same product group ?
NY11 NY11-1 A
NY11 NY11-2 A
NY12 NY12-2 A
NY13 NY13-1 A

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-01 : 23:54:58

based on your very limited sample data and given output, what i feel is its just a matter of adding a simple filter as

WHERE Status='A'

to your select query which selects data from table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2011-09-02 : 00:18:54

Let me be more specified.

Product_ID Product_Group Status Qty
NY11-1 NY11 A 1
NY11-2 NY11 A 2
NY12-1 NY12 I 2
NY12-2 NY12 A 3
NY13-1 NY13 A 4

The results that I expect is as below:
Product_Group Product_ID Status Qty
NY11 NY11-1 A 1
NY11 NY11-2 A 2
NY12 NY12-2 A 5
NY13 NY13-1 A 4

What I need is sum the Qty if the product group is same but status is not same. Let see product group NY12, there are two products within this group with one status is 'I' and another is 'A'. So this record should be sum together into one.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 00:24:49

sounds like below as per latest explanation


SELECT t.Product_Group, t.Product_ID, t.Status,t.Qty + COALESCE(t1.AltQty,0)
FROM Table t
OUTER APPLY (SELECT SUM(Qty) AS AltQty
             FROM Table
             WHERE Product_Group = t.Product_Group
             AND Status <> 'A'
            )t1
WHERE t.Status='A'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 00:25:57

One doubt, can there more than one record for the same Product_Group with same status value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources