Query ????? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query ?????

Author

Topic

vip
Starting Member

4 Posts

Posted - 2011-06-01 : 14:48:32

hello,
My question is as follows I have 2 tables table1 containing (tid,name )and table2 containing (id,tid,cost,quantity) and table1 is related to table2 by tid for example
Table1 Table2
tid name id tid cost quantity
1 X 1 1 10 1
2 X 2 3 20 2
3 Y 3 4 5 5
4 Y

in the above I have 2 tables if I go to table1 and check all the values whose tid<=2 are of name X and tid>=3 have name Y so what I need is to make a query to get the total sum of cost and quantity for those having tid>=3 and tid <=2

the final result should be as follows:

Total Quantity of type X | Total Cost of X| total Quantity of type Y| Total Cost of Y

here is my query but it doesn't give the result as required:
select sum(quantity),sum(cost*quantity) from table1 where tid<=2 groupby tid

select sum(quantity),sum(cost*quantity) from table1 where tid>=3 groupby tid

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-01 : 15:10:01

Could you post what your expected result should be?

Jim

Everyday I learn something that somebody else already knew

latch
Yak Posting Veteran

62 Posts

Posted - 2011-06-01 : 15:22:16

These query are providing results as these:

select sum(quantity),sum(cost*quantity) from table2 where tid<=2 group by tid
(No column name) (No column name)
1 10

select sum(quantity),sum(cost*quantity) from table2 where tid>=3 group by tid

(No column name) (No column name)
2 40
5 25

i think those values are right. if you want all the results at same time then use UNION.

provide a sample of your result to better guide you.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-01 : 15:40:49

Here's my guess based on little or no information and the fact that I'm leaving in 5 minutes

DECLARE @Table1 TABLE (tid int identity (1,1), name char(1))
DECLARE @Table2 TABLE (id int identity(1,1),tid int,cost int,qty int)
INSERT INTO @Table1
SELECT 'X' UNION ALL
SELECT 'X' UNION ALL
SELECT 'Y' UNION ALL
SELECT 'Y'

INSERT INTO @Table2
SELECT 1,10,1 UNION
SELECT 3,20,2 UNION
SELECT 4,5,5

;with cte
AS
(
SELECT NewT.[Name],NewT.Total,NewQty.Qty
FROM
(
select (t1.tid +1)/2 as NewTID
,[name]
,count(*) as Total
from @table1 t1
group by (t1.tid +1)/2 ,[name]
) NewT

INNER JOIN

(select (t2.tid +1)/2 as NewTID
, sum(t2.Cost*t2.qty) as QTY
from @table2 t2
group by (t2.tid +1)/2
)NewQty

ON

Newt.NewTid = NewQty.NewTid
)

select
MAX(CASE WHEN [Name] = 'X' THEN Total END) as TotalX
,MAX(CASE WHEN [Name] = 'X' THEN qty END) as TotalXCost
,MAX(CASE WHEN [Name] = 'Y' THEN Total END) as TotalY
,MAX(CASE WHEN [Name] = 'Y' THEN qty END) as TotalYCost
from cte

Jim

Everyday I learn something that somebody else already knew

vip
Starting Member

4 Posts

Posted - 2011-06-01 : 15:41:48

sample Output:

Table1:
tid Name
1 X
2 X
3 Y
4 Y

Table2:
id tid cost quantity
1 1 10 5
2 3 11 2
3 4 50 1
4 2 9 3

Output:

TotalCostX TotalQuantityX TotalCOSTY TOTALQUANTITYY
(10*5)+(9*3) 5+3 (11*2)+(50*1) 2+1

vip
Starting Member

4 Posts

Posted - 2011-06-01 : 23:02:44

If I make the following query it won't return the result in the specified output

select sum(quantity),sum(cost*quantity) from table1 where tid<=2 groupby tid
UNION
select sum(quantity),sum(cost*quantity) from table1 where tid>=3 groupby tid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources