Group by a field and see all the values of each record - SQL

Question

Group by a field and see all the values of each record - SQL

Navigation

#1 by (2 votes)
#2 by (0 votes)

3

I need to group by date a table with several dates with a specific value for each date. For example:

01/01/2016 10
01/01/2016 20
01/01/2016 30
02/01/2016 40
02/01/2016 50
...

The result should be:

01/01/2016 10 20 30
02/01/2016 40 50

This I have to get in a PostgreSQL table that I manage with Sequelize and NodeJS .

    var options = {
    where: {mes: req.body.resumen.mes, any: req.body.resumen.any},
    group: 'dia',
    order: [
        ['fecha', 'ASC']
    ]
};
models.Lectura.findAll(options).then(function(lecturas) {
    res.render('lecturas/resumen', {lecturas: lecturas,  errors: []});
}).catch(function(error){next(error)});

When I read about readings, I only get the last one from each group, something like this:

01/01/2016 30
02/01/2016 50

How could I solve it?

sql nodejs postgresql sequelize.js

asked by Jota 09.01.2017 в 20:15

source

2 answers

0

This is the nodejs code to solve with Sequelize:

var options = {

        where: {mes: req.body.resumen.mes, any: req.body.resumen.any},

        include: [{model: models.Lectura, order: 'codigo'}],

        order: [
            ['fecha', 'ASC']
        ]

    };

    models.Parte.findAll(options).then(function(partes) {

        res.render('partes/resumen', {partes: partes, errors: []});

    }).catch(function(error){next(error)});

In options, the records in the Part table that meet the condition of month and year are selected, and their details are included in the Reading table. Then just iterate over each master record and its details.

answered by 13.01.2017 в 18:41

How to execute 2 or more stored procedures with mysqli in php? Doubt treeMap in Java

score 2 · Accepted Answer

I do not know anything about node.js and sequelize.js, so I can not help you on that side.

But I can leave you a model of the SQL query class in PostgreSQL that allows you to group the records in that way. And then you can adjust the query according to your need.

Assuming a tbl table with the following structure and data:

create table tbl (
  fecha date not null,
  valor int not null
);

insert into tbl (fecha, valor)
values
('2016-01-01', 10),
('2016-01-01', 20),
('2016-01-01', 30),
('2016-02-01', 40),
('2016-02-01', 50);

The following query, using the string_agg function returns the results as you ask:

select fecha, string_agg(valor::text, ' ' order by valor)
  from tbl
 group by fecha;

Demo