Problem with 'select distinct'

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

Problem with 'select distinct'

Author

Topic

MGA
Starting Member

28 Posts

Posted - 2010-04-26 : 04:31:38

hello every1;
i have the following tables

- Products
(
Prduct_Id int primary key,
Product_Name varchar(50)
)

- Adding_Products
(
Add_Id int primary key,
Product_Id foreign key Products(Product_Id),
Quantity int
)
if the data in the products is

Product_Id Product_Name
1 a
2 b
3 c
and the data in the Adding_Products is

add_Id Product_Id Quantity
1 1 5
2 2 10
3 1 2
4 1 3
5 3 20
6 3 2

How to select the following data :

Product_Name Total_Quantity
a 10
b 10
c 22

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-04-26 : 04:40:12

select a.Product_Name,sum(isnull(b.Quantity,0)) from products a left join Adding_Products b on a.Product_Id=b.Product_Id

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-26 : 05:03:02

[code]
SELECT P.Product_Name, Total_Quantity = COALESCE(D.Sum, 0)
FROM Products P
OUTER APPLY (SELECT SUM(Quantity) AS Sum
FROM Adding_Product
WHERE Product_ID = P.Product_ID) D

Also:
SELECT Product_name,
COALESCE(
(SELECT SUM(Quantity)
FROM Adding_Products
WHERE P.product_ID = product_id), 0) AS Total_Quantity
FROM Products P
[/code]

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-26 : 05:08:36

Your query is not correct. You missed GROUP BY clause

quote:
Originally posted by sakets_2000

select a.Product_Name,sum(isnull(b.Quantity,0)) from products a left join Adding_Products b on a.Product_Id=b.Product_Id

Sachin.Nand

2937 Posts

Posted - 2010-04-26 : 05:30:09

quote:
Originally posted by ms65g

Your query is not correct.

quote:
Originally posted by sakets_2000

select a.Product_Name,sum(isnull(b.Quantity,0)) from products a left join Adding_Products b on a.Product_Id=b.Product_Id

Yes.
Because he forgot to put a group by clause for Product_Name.

PBUH

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-26 : 06:04:19

quote:
Originally posted by ms65g

Your query is not correct.

quote:
Originally posted by sakets_2000

select a.Product_Name,sum(isnull(b.Quantity,0)) from products a left join Adding_Products b on a.Product_Id=b.Product_Id

Yes, it needs a "GROUP BY a.Product_Name". However, the left join method is much faster than the outer apply or subquery methods. Run this and then look at the messages to see the difference:

CREATE TABLE Products
(
Product_Id int primary key,
Product_Name varchar(50)
)
GO

CREATE TABLE Adding_Products
(
Add_Id int primary key,
Product_Id INT CONSTRAINT FK_Adding_Products 
		foreign key (Product_Id) REFERENCES Products(Product_Id),
Quantity int
)
GO

SET NOCOUNT ON
DECLARE @Count INT SET @Count = 0

WHILE @Count < 1000
BEGIN
	INSERT INTO Products
	SELECT @Count, CAST(@Count AS VARCHAR(50))
	SET @Count = @Count + 1
END

SET @Count = 0

WHILE @Count < 100000
BEGIN
	INSERT INTO Adding_Products
	SELECT @Count, RAND() * 1000, RAND() * 1000
	SET @Count = @Count + 1
END
GO

SET STATISTICS IO ON
GO 

SET STATISTICS TIME ON
GO 


PRINT '

Method 1'
select a.Product_Name,sum(isnull(b.Quantity,0)) 
from products a 
left join Adding_Products b 
	on a.Product_Id=b.Product_Id
GROUP BY a.Product_Name

PRINT '

Method 2'
SELECT P.Product_Name, Total_Quantity = COALESCE(D.Sum, 0)
FROM Products P
OUTER APPLY (SELECT SUM(Quantity) AS Sum
               FROM Adding_Products
              WHERE Product_ID = P.Product_ID) D

PRINT '

Method 3'
SELECT Product_name,
COALESCE(
(SELECT SUM(Quantity) 
   FROM Adding_Products
  WHERE P.product_ID = product_id), 0) AS Total_Quantity
FROM Products P
GO

SET STATISTICS IO OFF
GO 

SET STATISTICS TIME OFF
GO 

DROP TABLE Adding_Products
DROP TABLE Products

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

portia_in_sql
Starting Member

1 Post

Posted - 2010-04-29 : 05:23:54

Lets keep it simple and sweet...

Following query will give you the desired output:

select b.Product_Name,SUM(Quantity) As Total_Quantity
from Adding_Products a JOIN Products b
ON a.Product_Id=b.Prduct_Id
GROUP BY b.Product_Name

Portia

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources