having problems in a query involving sum()

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

SQL Server 2008 Forums

Transact-SQL (2008)

having problems in a query involving sum()

Author

Topic

shantanu88d
Starting Member

35 Posts

Posted - 2011-04-25 : 05:30:48

i have a table purchase_master, in which i have available quantity.
sr_no model_brand model_number qty price
--------- ------------------- ---------------------- ------------- -----------------
1 Samsung Omnia 7 HD 10 200000.00
2 Nokia N72 12 30000.00
3 Nokia N72 20 3000.00

in another table i have assigned quantity.
sr_no model_no mobile_brand alotted_pf_no
---------------- ----------------------- ------------------------ ------------------------
11 Omnia 7 HD Samsung 223344

I had bought 11 quantities of Samsung mobile one order. Now i want that total available quantity should be seen. Which is 11 because available qty is 10 and on samsung mobile is alotted. So total 11 were bought. How can we achieve this through query ???
Please Help !!

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-25 : 06:04:45

CREATE TABLE #Table1 (sr_no INT, model_brand VARCHAR(50), model_number VARCHAR(50), qty INT, price MONEY)
GO
INSERT INTO #Table1
SELECT 1 ,'Samsung', 'Omnia 7 HD', 10, 200000.00 UNION ALL
SELECT 2 ,'Nokia', 'N72', 12, 30000.00 UNION ALL
SELECT 3 ,'Nokia', 'N72', 20, 3000.00

GO
CREATE TABLE #Table2 (sr_no INT, model_no VARCHAR(50), mobile_brand VARCHAR(50), alotted_pf_no VARCHAR(50))
INSERT INTO #Table2
SELECT 11, 'Omnia 7 HD', 'Samsung', 223344

GO
--YOUR REQURIED QUERY
SELECT T2.sr_no,
T2.mobile_brand,
T2.model_no,
T2.alotted_pf_no,
T1.TotalQty
FROM #Table2 T2
INNER JOIN ( SELECT model_brand,
model_number,
SUM(QTY) AS TotalQty
FROM #Table1
GROUP BY model_brand,model_number
) T1 ON T1.model_brand = T2.mobile_brand
AND T1.model_number = T2.model_no

GO
DROP TABLE #TABLE1
DROP TABLE #TABLE2

--------------------------
http://connectsql.blogspot.com/

shantanu88d
Starting Member

35 Posts

Posted - 2011-04-25 : 06:30:08

In upper part of the query you submitted, you have hardcoded rows in table. I dont want that. Query should give answer dynamically. Please correct if I am wrong. I may be wrong, I dont have a lot of knowledge of sql :|

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-25 : 06:52:42

NOP, i just created temporary tables to hold rows, actual query starts with comments --YOUR REQURIED QUERY

--------------------------
http://connectsql.blogspot.com/

shantanu88d
Starting Member

35 Posts

Posted - 2011-04-25 : 06:59:54

ok sorry about that. I will check this out ! Thanks for helping !!

shantanu88d
Starting Member

35 Posts

Posted - 2011-04-26 : 01:47:01

This also worked


select model_brand,sum(qty)
from
	(
		(
			select model_brand,sum(qty)as qty 
			from mobile_master 
			group by model_number,model_brand
		)
		union
		(
			select model_brand,count(model_number)as qty 
			from telephone_alottment 
			group by model_number,model_brand
		)
	)m
where model_brand = model_brand
group by m.model_brand