Multiple Selects in one statement

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)

Multiple Selects in one statement

Author

Topic

phate06
Starting Member

17 Posts

Posted - 2013-09-04 : 05:49:44

Hi Guys

I need to return three individual results based on one table, below is my query but it does not like it, I am getting syntax errors see below too - can any one advise?

select
(
Select (COUNT (CustomerID)) as 'Trade Account' from customer where CustomerCode like 'TRC%' and Deleted = 0
Select (COUNT (CustomerID)) as 'Credit Account'from Customer where CustomerCode not like 'TRC%' and Deleted=0 and CashAccount=0
Select (COUNT (CustomerID)) as 'Cash Account' from Customer where CustomerCode like 'CAS%' and Deleted=0 and CashAccount=1
)From Customer

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'Select'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-04 : 06:01:15

--May be this you want?

Select
	(COUNT (CASE WHEN CustomerCode like 'TRC%' and Deleted = 0 THEN CustomerID END)) as 'Trade Account' ,
	(COUNT (CASE WHEN CustomerCode not like 'TRC%' and Deleted=0 and CashAccount=0 THEN CustomerID END )) as 'Credit Account',
	(COUNT (CASE WHEN CustomerCode like 'CAS%' and Deleted=0 and CashAccount=1 THEN CustomerID END )) as 'Cash Account' 
From Customer

--
Chandu

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-09-04 : 06:09:06

Select
(COUNT (CASE WHEN CustomerCode like 'TRC%' and Deleted = 0 THEN CustomerID END)) as 'Trade Account' ,
(COUNT (CASE WHEN CustomerCode not like 'TRC%' and Deleted=0 and CashAccount=0 THEN CustomerID END )) as 'Credit Account',
(COUNT (CASE WHEN CustomerCode like 'CAS%' and Deleted=0 and CashAccount=1 THEN CustomerID END )) as 'Cash Account'
From Customer

veeranjaneyulu

phate06
Starting Member

17 Posts

Posted - 2013-09-04 : 06:09:42

Thank you Sir, If I could See you I would probably Kiss you