Combining queries - 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

Combining queries

Author

Topic

engcanada
Starting Member

39 Posts

Posted - 2012-05-02 : 11:49:45

I need to combine the following 3 queries into one:

select count(id) as totalA where country=1
select count(id) as totalB where country=2
select count(id) as totalC where country=3

Thank you

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-02 : 12:01:26

quote:
Originally posted by engcanada

I need to combine the following 3 queries into one:

select count(id) as totalA where country=1
select count(id) as totalB where country=2
select count(id) as totalC where country=3

Thank you



What output do you need?

engcanada
Starting Member

39 Posts

Posted - 2012-05-02 : 12:17:09

totalA, totalB, totalC

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-02 : 12:21:51

Not clear with your output...You want table name to become column name

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-02 : 12:28:25

Homework? test? Interview?

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

engcanada
Starting Member

39 Posts

Posted - 2012-05-02 : 12:32:59

Sorry, I forgot to put the table name:
select count(id) as totalA from table1 where country=1
select count(id) as totalB from table1 where country=2
select count(id) as totalC from table1 where country=3

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-02 : 12:33:20

quote:
Originally posted by engcanada

I need to combine the following 3 queries into one:

select count(id) as totalA where country=1
select count(id) as totalB where country=2
select count(id) as totalC where country=3

Thank you


Is data in your single table or in different table? In your query you have not mentioned the table name .. Check your query again and let us know.

If this is from single table than you can do like this 

select country,count(1) from tab 
group by country

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-02 : 13:10:53

select count(CASE WHEN country=1 THEN id END) as totalA,
count(CASE WHEN country=2 THEN id END) as totalB,
count(CASE WHEN country=3 THEN id END) as totalC
from table1 where country BETWEEN 1 AND 3

engcanada
Starting Member

39 Posts

Posted - 2012-05-02 : 14:40:31

The following seems to be a more efficient query; however I am a bit confused:
select country, count(1) from tab group by country
How do I call each total, ie: totalA, totalB, totalC?
Everything is from one table lets say called EMPLOYER

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-03 : 06:45:10

People are finding it hard to understand what your requirement is because you haven't posted the DDL of you table nor have you posted any sample data.
People here need for a sample data set to work with if they are to provide a solution to your query.
Please provide DDL of the tables and some readily consumable sample data.
Help us in helping you.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-05-03 : 09:37:34

quote:
Originally posted by robvolk

select count(CASE WHEN country=1 THEN id END) as totalA,
count(CASE WHEN country=2 THEN id END) as totalB,
count(CASE WHEN country=3 THEN id END) as totalC
from table1 where country BETWEEN 1 AND 3

I prefer this which will avoid returning warnings on NULLs

select sum(CASE WHEN country=1 THEN 1 else 0 END ) as totalA,
sum(CASE WHEN country=2 THEN 1 else 0 END) as totalB,
sum(CASE WHEN country=3 THEN 1 else 0 END) as totalC
from table1 where country BETWEEN 1 AND 3

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources