Home / Questions / 329499
Accepted
Juan Andres solanas
Asked: 2020-02-15 09:46:06 +0800 CST 2020-02-15 09:46:06 +0800 CST

How to group records based on their primary key

  • 772

how are they ? I need to do the following:

Show the number of different companies that have made requests and the difference with respect to the total number of requests.

The problem is that I can't find a way to count separately the companies that made requests (I know there are 3). The tables are these:

companies: cuit(pk), company name requests_companies: cuit(pk - fk of companies), cod_cargo.

Try to type a query like this:

     select count(select count(*) from solicitudes_empresas group by cuit) as 
     cantidad, (select count(*) from solicitudes_empresas) - (select count(*) from 
     solicitudes_empresas) as diferencia 
     from empresas as e 
     inner join solicitudes_empresas as sol 
     on e.cuit =  sol.cuit;

But it doesn't work for me since it tells me:

Subquery returns more than 1 row

and I'm doing a count, so I don't know what it will be! Thank you very much!

Edit: If I do the following:

select e.cuit  from empresas as e inner join solicitudes_empresas as sol
    on e.cuit = sol.cuit group by e.cuit;

It effectively returns me 3 records:

enter image description here

But if I do count(*) to that same query, it should return the number 3, but it returns the following: enter image description here

mysql

1 Answers

  1. Best Answer

    The solution would be:

    select count(empresaid) 
from (
select cuit empresaid, count(cuit) total 
from solicitudes_empresa 
group by (cuit)) subconsulta

    The subquery will return one record for each company that has a request. The outer query will give you the count of these records.

    • 3