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

Query Doubt

Author

Topic

anishchelat
Starting Member

1 Post

Posted - 2010-08-03 : 00:23:35

i have a table named transaction
it contains fields as below

customerid int
quantity int
type varchar(50)

Table Looks like

customerid quantity type
1 10 delivery
2 5 delivery
1 5 return
2 1 return
3 10 delivery
4 10 delivery
3 5 return
1 5 delivery

i want to get output like this
customerid delivery return
1 15 5
2 5 1
3 10 5
4 10 0

so on
How to write sql query for the above?
plz help me
Thanks in advance

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-03 : 00:37:46

try this:



create table deliver
(customerid  int
,quantity  int
,type varchar(20)
)
insert into deliver
select  1,10,'delivery'
union all select 2,5,'delivery'
union all select 1,5,'return'
union all select 2,1,'return'
union all select 3,10,'delivery'
union all select 4,10,'delivery'
union all select 3,5,'return'
union all select 1,5,'delivery'


select 
d.customerID
,isnull((select sum(d1.quantity) 
	from deliver as d1 
	where d1.customerid = d.customerID and type = 'delivery'),0) as Delivery
,isnull((select sum(d1.quantity) 
	from deliver as d1 
	where d1.customerid = d.customerID and type = 'return'),0) as [Return] 
 from deliver as d
 group by d.customerID
 order by d.customerID

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-03 : 00:38:55

Try this:

select customerid, sum( Case when type = 'delivery' then quantity else 0 end ) delivery,
sum( Case when type = 'return' then quantity else 0 end ) [return]
from [transaction]
group by customerid

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 01:39:22

if sql 2005


SELECT customerid,[delivery],[return]
FROM Table
PIVOT (SUM(quantity) FOR type IN ([delivery],[return]))p

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

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-08-03 : 01:39:37

quote:


select 
d.customerID
,isnull((select sum(d1.quantity) 
	from deliver as d1 
	where d1.customerid = d.customerID and type = 'delivery'),0) as Delivery
,isnull((select sum(d1.quantity) 
	from deliver as d1 
	where d1.customerid = d.customerID and type = 'return'),0) as [Return] 
 from deliver as d
 group by d.customerID
 order by d.customerID

can you give elaboration to your code for condition "isnull" wt it means

With Regards
Kashyap M

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 01:42:56

its just checking if subquery is returning nulls and if its null return 0 instead

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

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-08-03 : 01:43:51

ok i got it

With Regards
Kashyap M

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources