CODEWITHSUNDEEP
sqlpostgresql
Frechi
Frechi

Reputation: 13

Postgresql At least 2 sales in the same day

Sorry about my english I have to get all the records what has the same date at least 2 times , i mean i want to know whos seller make at least 2 sales in the same date.

Table A

id          Name    Lastname     Seller     Date     Item
23456     Roberto Rodriguez    jvazquez 01/01/2013   auto
23423     Roberto Rodriguez    jvazquez 01/01/2013   moto
5654      Julián Domínguez     rfleita  05/02/2013   lancha
34534653  Romina Santaolaya    jvazquez 02/02/2013   moto
346534    Romina Santaolaya    rfleita  05/02/2013   auto

Result

23456     Roberto Rodriguez    jvazquez 01/01/2013   auto
23423     Roberto Rodriguez    jvazquez 01/01/2013   moto
346534    Romina Santaolaya    rfleita  05/02/2013   auto
5654      Julián Domínguez     rfleita  05/02/2013   lancha

Upvotes: 1

Views: 72

Answers (3)

Jimmy Stenke
Jimmy Stenke

Reputation: 11220

This should give you the result asked for:

SELECT *
    FROM TableA
    WHERE (Date, Seller) IN (
        SELECT Date, Seller
            FROM TableA
            GROUP BY Date, Seller
            HAVING COUNT(*) >= 2
    )

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 416059

SELECT seller, Date, COUNT(*) "Sales"
FROM TableA
GROUP BY Date, seller
HAVING COUNT(*) >= 2

Upvotes: 3

user330315
user330315

Reputation: 

select id, 
       Name,
       Lastname,
       Seller,
       Date,
       Item
from (
  select id, 
         Name,
         Lastname,
         Seller,
         Date,
         Item,
         count(*) over (partition by date, seller) as cnt
   from the_table
) t
where cnt >= 2;

SQLFiddle example: http://www.sqlfiddle.com/#!12/7c0cc/4

If you don't need the additional columns, Joel's answer will be quicker.

Upvotes: 1

Related Questions