Count repeated cases in SQL

Navigation
1

I do not have much experience in SQL and I'm making a mess to solve something that almost certainly is not very complex.

I have a table that has a column with an alphanumeric code: 

columnA {xa1, xa2, xa3, xa3, xa3, xa4, xa4, xa5, xa6, xa6, etc.}

I am trying to create a new table that tells me the following:

As of xa1, xa2 i xa5 there is 1 in each case, I have 3 events of 1, 1 event of 3 reptocations (xa3), 2 events of 2 repetitions (xa4, xa6).

I have managed to create the table that tells me the number of events of each code: 

SELECT   rules.codventa,
     COUNT(rules.codventa) AS sum INTO suma 
FROM     rules 
GROUP BY rules.codventa 
ORDER BY rules.codventa DESC;

I have something like this:

  • xa1 - 1
  • xa2 - 1
  • xa3 - 3
  • xa4 - 2
  • xa5 - 1
  • xa6 - 2

Now I can not get the final table that says:

  • 1 - 3
  • 2 - 2
  • 3 - 1

Any ideas?

Thanks

EDIT: add the creation code of the table 

CREATE TABLE gourmet.rules
(
  codventa character(25) NOT NULL,
  nombretienda character(100) NOT NULL,
  codproducto character(100) NOT NULL,
  unidades smallint NOT NULL,
  precioventa numeric(8,4) NOT NULL
)

The primary key corresponds to coding

    
asked by alwayslearn 31.12.2017 в 12:08
source

3 answers

0

I've got a code that works for me based on Patricio's proposal. 

SELECT   sum,
     COUNT(1)
     FROM (SELECT   rules.codventa,
                    COUNT(rules.codventa) AS sum
                    FROM     rules 
                    GROUP BY rules.codventa 
                    ORDER BY rules.codventa DESC
    )AS tablaFinal
    GROUP BY sum
    ORDER BY sum;

Thank you all for your help and happy new year!

    
answered by 01.01.2018 / 19:15
source
1

If I did not get it wrong, what you can do is: 

SELECT   suma,
         COUNT(1)
         FROM (SELECT   rules.codventa,
                        COUNT(rules.codventa) AS sum INTO suma 
                        FROM     rules 
                        GROUP BY rules.codventa 
                        ORDER BY rules.codventa DESC
        )
        GROUP BY suma;

That is, we take your original query and use it as a subquery to be able to count the different cases of suma

    
answered by 31.12.2017 в 15:10
0

If the codes in "codventa" are in an array, you can use the function unnest to transform it into a pseudotabla: 

select id, codigo, count(*) as cantidad
  from la_tabla, unnest(codventa) codigo
  group by id, codigo
  order by id, codigo;

This would show the results similar to the ones you can already get. To count the quantities using that query or any other you can put it into another query and use it as a source for another select: 

select cantidad, count(*) as ocurrencias
  from (
    select id, codigo, count(*) as cantidad
      from la_tabla, unnest(codventa) codigo
      group by id, codigo
      order by id, codigo
  ) x
  group by cantidad
  order by cantidad;

To prove this I used this table definition: 

create table la_tabla(
    id integer primary key,
    codventa text[]
);

insert into la_tabla values
  (1, '{xa1, xa2, xa3, xa3, xa3, xa4, xa4, xa5, xa6, xa6}');

Results

Online example

Here is an example that runs online: link

I recommend you when you ask questions try on that page or sqlfiddle or the one you prefer, put the definition of tables on which you are asking the question and an example of data. That helps us help you.

    
answered by 31.12.2017 в 19:03
Error getting the name of the client PC from the server Command equivalent to 'hostname' in Windows