• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Count occurences in the column for specific value in another column

 
Kirill Varivoda
Greenhorn
Posts: 20
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm working with embedded Derby and I'm new in databases.
I have a table SaveSites in a database like this:



I need to count all INNERURL for http://hostingmaks.com/ and all INNERURL for http://hostingmaks.com/category/news/

So result should be like this:
http://hostingmaks.com/ 3 INNERURL
http://hostingmaks.com/category/news/ 2 INNERURL

And finally save these results in another database.

Is there is a way to do it? I can't solve it yet.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can use the SQL aggregate function COUNT with a GROUP BY clause, plus a WHERE clause to restrict the search to the URLs you are interested in. More generally, you need to learn some SQL if you are going to work with databases. SQL is immensely powerful and flexible and is designed for relational databases. Learn how to use the right tool for the job and you will find it much easier to achieve your goals.
 
Kirill Varivoda
Greenhorn
Posts: 20
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
chris webster wrote:You can use the SQL aggregate function COUNT with a GROUP BY clause, plus a WHERE clause to restrict the search to the URLs you are interested in. More generally, you need to learn some SQL if you are going to work with databases. SQL is immensely powerful and flexible and is designed for relational databases. Learn how to use the right tool for the job and you will find it much easier to achieve your goals.


Thanks for advice. I've found a solution.

I have another problem. How NOT to count Null and Emtpy values?

I tried to add into the query "having externalUrl not null" but it doesn't work.

The error is:
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Remember you can use a WHERE clause to include/exclude the records you count. Try "WHERE ... IS NOT NULL".
 
Kirill Varivoda
Greenhorn
Posts: 20
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
chris webster wrote:Remember you can use a WHERE clause to include/exclude the records you count. Try "WHERE ... IS NOT NULL".


Thanks for pointing out. Actually this worked:

So this is solved, but I still do not know how NOT to count empty values.

Although I tried to avoid it but I still do not know why # appears in my table. As I understand from output it means that I have empty values. I set Default value (while creating a table) as null but I still got this.
So, I've got a table like this


So I either do so that sql will not count empty values or make impossible for empty value to appear in my table.

This is the key code for saving data to the talbe. allInnerLinks - this is the second column, which I need to count afterwards). Probably here I've made a mistake. Instead of # should be null.


Appreciate for any help!
 
Kirill Varivoda
Greenhorn
Posts: 20
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
chris webster wrote:Remember you can use a WHERE clause to include/exclude the records you count. Try "WHERE ... IS NOT NULL".


Oh, finally! I did it. With this stuff I count what I need, it seems:
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Kirill Varivoda wrote:
chris webster wrote:Remember you can use a WHERE clause to include/exclude the records you count. Try "WHERE ... IS NOT NULL".


Oh, finally! I did it. With this stuff I count what I need, it seems:

I suggest you get rid of the default value of (text) 'null' as it just confuses things. If there is no value, just leave the field as (genuine) NULL. Databases and SQL know how to deal with NULL values, and so do other developers. If you have a "magic value" like 'null' instead of using the standard NULL value, then it's just another application-specific piece of information that will get forgotten and end up causing problems later.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic