Single query to return counts over different IDs in a single record

Question

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?

Answer 1

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

Answer 2

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 

Answer 3

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;

Answer 4

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

Answer 5

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