I have this one SQL string query:

SELECT company_id, COUNT(company_id)
FROM core.non_ceased_companies_prod_unit
GROUP BY company_id
HAVING COUNT(company_id) > 1

The result is:

I want to have a sum of second column (which is "No column name") and count of rows (now it's 4)

So the result should be:

{
  companies: 4
  productions: 11 //(2 3 2 4)
}

Query should be in Sequelize variant

CodePudding user response：

If you create an SQL view, you can select from that view:

Note: The query and schema below can be executed via SQL Fiddle.

Stored procudure

CALL companyProductions();

Result

Sequalize

sequelize
  .query('CALL companyProductions()')
  .then(data => console.log(data)); // [{ companies: 4, productions: 11 }]

Schema

Using delimiter: //

CREATE TABLE company (
  id varchar(255) NOT NULL,
  name varchar(255) NOT NULL,
  PRIMARY KEY (id)
)//

CREATE TABLE non_ceased_companies_prod_unit (
  company_id varchar(255) NOT NULL,
  units int,
  CONSTRAINT FK_CompanyId FOREIGN KEY (company_id)
  REFERENCES company(id)
)//

CREATE VIEW unit_frequency_by_company AS
  SELECT company_id, COUNT(company_id) AS frequency
    FROM non_ceased_companies_prod_unit
    GROUP BY company_id
    HAVING frequency > 1//

CREATE PROCEDURE companyProductions()
BEGIN
  SELECT COUNT(*) AS companies, SUM(frequency) AS productions
    FROM unit_frequency_by_company;
END//

Schema data

INSERT INTO company(id, name)
  VALUES ('DK-10001560', 'Company A'),
         ('DK-10001587', 'Company B'),
         ('DK-10005809', 'Company C'),
         ('DK-10006511', 'Company D')//
 
INSERT INTO non_ceased_companies_prod_unit(company_id, units)
  VALUES ('DK-10001560', 1),
         ('DK-10001560', 1),
         ('DK-10001587', 1),
         ('DK-10001587', 1),
         ('DK-10001587', 1),
         ('DK-10005809', 1),
         ('DK-10005809', 1),
         ('DK-10006511', 1),
         ('DK-10006511', 1),
         ('DK-10006511', 1),
         ('DK-10006511', 1)//

CodePudding user response：

Please try following query :

SELECT COUNT(company_id) as companies, SUM(company_cnt) as productions FROM (
SELECT company_id, COUNT(company_id) as company_cnt
FROM core.non_ceased_companies_prod_unit
GROUP BY company_id
HAVING COUNT(company_id) > 1
)foo