Member Avatar for strRusty_gal

Hi Everyone,
Thanks for your time.

I have a question about COUNT in sql. Does anyone know how to display the COUNT if it is equal to 0?

Please feel free to ask me any question if you don't understand my enquire.

Thanks for the post.

  • 3 Contributors
  • 2 Replies
  • 92 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by ChrisPadgham
Member Avatar for urtrivedi 
select last_name, count(*) total from user_master 
group by last_name
having count(*) = 1

This query will find last_name that occurs only once in the table.
so you must use having with aggregate functions.

Edited by urtrivedi because: n/a
Member Avatar for ChrisPadgham

The difficulty is that count() works against summarising existing rows in a table. By definition a count of 0 means that no rows exist so nothing will appear. How would SQL know what the missing values were. To address the problem you will need to set up a reference table with all the valid values in it and do an outer join against the table with the data in it.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.