CODEWITHSUNDEEP
sql-server-2008
Marcus25
Marcus25

Reputation: 883

Finding the Count of Columns in Sql server under different conditions

Below query will give me publication count for each library

Select lib.Name, COUNT(pub.Id) From Library lib
INNER JOIN Publication lib.ID = pub.FK_Library_Id

But, In publication table, I have Free publications and Paid Publications and I want the count of Paid Publication and Free publications as well.

The below query gives me the result

Select lib.Name, COUNT(pub.Id), COUNT(pub1.Id) as 'Paid Publication COunt', 
COUNT(pub2.Id) as 'FreePublication COunt'
 From Library lib
INNER JOIN Publication pub ON lib.ID = pub.FK_Library_Id
LEFT JOIN Publication pub1 ON lib.ID = pub1.FK_Library_Id and pub1.Price > 0
LEFT JOIN Publication pub2 ON lib.ID = pub2.FK_Library_Id and pub2.Price =0

BUt this is at the cost of adding two more JOINS, which is not optimised.

How can i write the above query in a more effective and optimised way.

Thanks

Upvotes: 0

Views: 46

Answers (1)

Daniel PP Cabral
Daniel PP Cabral

Reputation: 1624

You could perhaps use a SUM with CASE statement, as follows:

Select lib.Name, COUNT(*),
SUM(CASE WHEN pub.Price > 0 THEN 1 ELSE 0 END) as 'Paid Publication COunt', 
SUM(CASE WHEN pub.Price = 0 THEN 1 ELSE 0 END) as 'FreePublication COunt'
 From Library lib
INNER JOIN Publication pub ON lib.ID = pub.FK_Library_Id
Group By lib.Name

Upvotes: 1

Related Questions