Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
select count(title_id)as algodata from titles where pub_id =1389  
select count(title_id)as binnet   from titles where pub_id =0877  
select count(title_id)as newmoon  from titles where pub_id =0736

The database used is pubs in SQL Server.

Can I use a single query to show the count of records for each publisher (here there are 3 publishers) in a single record?

share|improve this question

5 Answers 5

up vote 7 down vote accepted

Similar to @ypercubes but to get one row without 3 separate queries

select
     count(CASE WHEN pub_id = '1389' THEN title_id END) as algodata,
     count(CASE WHEN pub_id = '0877' THEN title_id END) as binnet,   
     count(CASE WHEN pub_id = '0736' THEN title_id END) as newmoon  
from titles
where pub_id IN ('1389', '0877', '0736') 

Also, decide if your values are numbers or strings and be consistent to avoid datatype conversions

share|improve this answer
    
You need to give the columns different aliases though. –  a_horse_with_no_name Aug 8 '12 at 9:19
    
@a_horse_with_no_name: oops. copy/paste error. Thanks –  gbn Aug 8 '12 at 9:34
    
it worked gbn thanks for the fast reply –  Biju jose Aug 8 '12 at 11:21

I will assert that you have a table

publishers(pub_id, pub_name) 

with the primary key pub_id and the unique key oub_name containing the rows

1389,'algodata'
0877,'binnet'
0736,'newmoon'

then the query

select p.pub_name,count(t.title_id)
from titles t, publishers p
where t.pub_id=p.pub_id
and p.pub_name in ('algodata','binnet','newmoon')
group by p.pub_name
/

will return something like

135 binnet
 21 newmoon
 24 algodata

this is a little bit different from your queries because in your queries the name of the publisher is the name of the column and not the value of a column

share|improve this answer

You can also use Pivot assuming you are on at least SQL Server 2005

SELECT [1389] AS algodata,
       [0877] AS binnet,
       [0736] AS newmoon
FROM   titles PIVOT (COUNT(title_id) FOR pub_id IN ([1389], [0877], [0736])) P 
share|improve this answer

Result in 3 rows:

SELECT pub_id 
     , COUNT(title_id) AS algodata 
FROM titles 
WHERE pub_id IN (1389, 877, 736)  
GROUP BY pub_id ;

To get the results in one row:

SELECT 
  ( select count(title_id) as algodata from titles where pub_id = 1389 ) AS algodata,
  ( select count(title_id) as binnet   from titles where pub_id = 0877 ) AS binnet,
  ( select count(title_id) as newmoon  from titles where pub_id = 0736 ) AS newmoon;
share|improve this answer
    
the first one is not the query i want but thanks for your info the second one is correct. ypercube thanks any way and for your fast reply –  Biju jose Aug 8 '12 at 11:22

Yes you can. You will need to use sub-queries to accomplish this though. The following link should guide you in the right direction:

http://msdn.microsoft.com/en-us/library/ms189623(SQL.105).aspx

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.