How to write the correct query for counting information in Postgresql?-CodePudding

I need the data from "machines_in_operation" to be written to "result", provided. The conditions are that he found all the data and recorded the number of them. The only thing they have different is the serial number and thanks to it you can understand how many models there are. The result table has the final result.

I am looking for the corresponding lines and if they are the same except for the serial number, then they need to be folded since I need to find out the total number of the client and this model. And plus to know which object is located on

My code:

select source, object, redirect, model, count(*)
from machines_in_operation 
group by source, object, redirect, model
HAVING count(*) > 1
ORDER BY redirect

the problem with my code is that if there is one record, it will not show and I do not know how to write to another table.

But it is necessary that the information is updated exactly the amount and if, for example, there is no information (redirect or object), then you should write them

Here are the data and . desired result sql --> https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=6edc46015a6ce5cec3b1fc3cbaa87371

CodePudding user response：

If I understand the question correctly, then you need to do this

CREATE TABLE machines_in_operation (
source text, object text, serial_number text, redirect text, model text);
CREATE TABLE result (
source text, object text, redirect text, model text, quantity int);

INSERT INTO result(source, object, redirect, model, quantity)
SELECT source, object, redirect, model, count(*)
FROM machines_in_operation
GROUP BY source, object, redirect, model
ORDER BY redirect;

or considering duplicates

INSERT INTO result(source, object, redirect, model, quantity)
SELECT source, object, redirect, model, count(DISTINCT serial_number)
FROM machines_in_operation
GROUP BY source, object, redirect, model
ORDER BY redirect;

Fiddle