Count in the Postgresql database

Asked

Viewed 153 times

0

Good morning .

I wanted to select in my bank and return the number of registered .

Example:

tb_client; column: dt_register;

in the dt_register column I have this:

2000-01-20
2001-10-05
1990-11-09
1990-07-16
1990-08-10

I wanted to make a select like this:

SELECT dt_cadastro, count(dt_cadastro)
FROM tb_cliente
WHERE dt_cadastro < 2000
GROUP BY dt_cadastro

I mean, I want to know how many are less than 2000 and I’m not getting.

Following the suggestion of @Motta, used this:

SELECT dt_cadastro , count(dt_cadastro )
FROM tb_cliente
WHERE  Extract(year from dt_cadastro ) = Extract(year from '1990-12-31'::DATE)
GROUP BY dt_nasc

Only the result was:

"1990-01-01";1
"1990-08-02";1
"1990-08-08";1

I needed the value to appear 3, it is possible?

sql postgresql

  • WHERE Extract(year from dt_cadastro) < 2000 https://www.postgresql.org/docs/9.6/static/functions-datetime.html

  • @Motta I will edit my question added your suggestion.

  • Try it this way SELECT count(dt_cadastro )&#xA;FROM tb_cliente&#xA;WHERE Extract(year from dt_cadastro ) = Extract(year from '1990-12-31'::DATE)&#xA;GROUP BY dt_nasc

  • @R.Santos did not give... appeared 1 1 1

  • Change group by to Group by dt_cadastro sees what the

  • @Vilma It worked out?

  • @R.Santos also does not. I managed using this: SELECT COUNT(*) FROM tb_client WHERE EXTRACT(YEAR FROM dt_cadastre) = 1980; Now I’ll see how to use this with the Hibernate criteria.

  • @Vilma try it this way then select count(*) from tb_cliente where dt_cadastro < '1999-12-31' I tested it here in my database and it worked out, if it works out just let me know

  • @R.Santos, it didn’t work. I’m using, as I said earlier: SELECT COUNT(*) FROM tb_client WHERE EXTRACT(YEAR FROM dt_cadastre) = 1980; and it’s working the way I want it to. I will just convert this to the Hibernate criteria.

  • Strange, in my database Postgresql worked, bringing only the value of Count, but it worked out that much better

Show 5 more comments

2 answers

0

The solution in your question is correct, but no SELECT you should put only the options you want to "see", see the SQL below if that’s what you really need:

SELECT COUNT(dt_cadastro) -- no select vai somente a informação que você quer ver
  FROM tb_cliente
 WHERE EXTRACT(YEAR FROM dt_cadastro) = EXTRACT(YEAR FROM '1990-12-31'::DATE)
 GROUP BY dt_nasc

-1

Solution found:

SELECT COUNT(*) FROM tb_client WHERE EXTRACT(YEAR FROM dt_cadastre) = 1980;

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.