Search code examples
selectfrequencyinformix

How to return a count result of only one column?

I have a table like this:

enter image description here

And would like to make a selection by period (date field), where I could return the registers, and a column with the total of registres matched with only one column.

For example: If I use this query:

SELECT date, product_type, operation, unit
FROM table
WHERE date BETWEEN '2019-08-26 00:00:00' AND '2019-08-26 23:59:59';

It must return:

enter image description here

But I wish I could return one more column, with the total of operations regardless period like this:

enter image description here

Where in this case, 6 is the frequency that "ajoy" appears in the table.

IMPORTANT! If I select a period where two or more operations are returned, so the query must be able to return their frequency as well.

Solution

  • I used part of your data and will include what I had for data in the table, but I believe this is what you want

    create table mytab (job int, operations char(8), prod char(8),  
ts_date datetime year to minute, unit int) lock mode row;

insert into mytab values(22, "ajoy","arrow","2020-05-11 08:51", 20);
insert into mytab values(22, "ajoy","arrow","2020-05-11 08:51", 20);
insert into mytab values(22, "ajoy","arrow","2020-05-11 08:51", 20);

insert into mytab values(22, "ajoy","arrow","2020-04-11 14:15", 20);
insert into mytab values(22, "ajoy","arrow","2020-04-11 14:15", 20);
insert into mytab values(22, "ajoy","arrow","2020-04-11 14:15", 20);

insert into mytab values(23, "dinn","curve","2020-05-11 08:51",1);
insert into mytab values(23, "dinn","point","2020-05-11 08:51",1);
insert into mytab values(23, "dinn","arrow","2020-04-11 08:51",1);

    The query:

    select job, operations, prod, ts_date, unit, (select count(*) from mytab b 
where b.operations = a.operations) total_operation  from mytab a where 
a.ts_date between "2020-05-11 08:50" and "2020-05-11 08:59"

    The above query gave me the following results which is I think what you were asking for:

        job operations prod     ts_date                 unit  total_operation

     22 ajoy       arrow    2020-05-11 08:51          20                6
     22 ajoy       arrow    2020-05-11 08:51          20                6
     22 ajoy       arrow    2020-05-11 08:51          20                6
     23 dinn       curve    2020-05-11 08:51           1                3
     23 dinn       point    2020-05-11 08:51           1                3

    This example is small and has doesn't include/account for indexes you may which to put on the table to speed query performance.