SQL Group query with more than one distinct attribute

Asked

Viewed 5,211 times

3

I have an application that generates approximately 25000 records per day in BD. This will generate a large amount of id’s per day in the user’s BD.

According to the user’s business rule, only failed inspections (is_ok = 0), must be registered, those that have been approved (is_ok = 1) may be discarded.

But the user needs a report with some data of the daily production made, as the total of model inspections made each day, how many were approved or failed, what time the first inspection was made and what time the last inspection was made.

Thinking of eliminating the fact of generating large volume of id’s generated per day, at the end of each day I could read this table of INSPECTIONS, record the ones that were failed in a table (INSPECOES_REPROVADAS), record daily production data in another table (PRODUCAO_DIARIA), delete all records from the INSPECTOES table, and finally, restart the ID’S counter thus preparing the table to start a new inspection record with id starting again at 1.

I need to do a job on a Mysql server to perform this task.

Remember that I have to record the inspections grouped by date and model, that is, on the same date I have inspections of more than one model. I can’t add inspections of different models to the same given.

Take the example:

INSPECOES
=========
id      is_ok   serial_number       date_inspection         path            mode    models_id   shift_id    line_id
1       1       BR11S140325004685   2014-08-27 09:50:36                     1       7           1           1
2       1       BR11S140325004663   2014-08-27 09:51:01                     1       7           1           1
3       1       BR11S140325004685   2014-08-27 10:16:39                     1       7           1           1
4       1       BR11S140325004663   2014-08-27 10:17:28                     1       7           1           1
5       0       BR11S140325004685   2014-08-27 10:41:35     c:/bla.abc      1       7           1           1
6       0       BR11S140325004685   2014-08-27 10:42:19     c:/bla.abc      1       7           1           1
7       0       BR11S140325004663   2014-08-27 10:42:34     c:/bla.abc      1       7           1           1
8       1       BR11S140325004685   2014-08-27 11:05:19                     1       7           1           1
9       1       BR11S140325004599   2014-08-27 11:07:33                     1       6           1           1
10      1       BR11S140325004599   2014-08-27 11:30:16                     1       6           1           1
11      1       BR11S140325004685   2014-08-27 11:36:42                     1       6           1           1
12      0       BR11S140325004666   2014-08-27 12:40:14     c:/bla.abc      1       6           1           1
13      0       BR11S140325004663   2014-08-27 12:42:34     c:/bla.abc      1       6           1           1
14      1       BR11S140325004685   2014-08-28 08:55:12                     1       7           1           1
15      1       BR11S140325004685   2014-08-28 08:56:22                     1       7           1           1
16      1       BR11S140325004663   2014-08-28 08:57:44                     1       7           1           1
17      0       BR11S140325004685   2014-08-28 08:59:12     c:/bla.abc      1       7           1           1
18      0       BR11S140325004685   2014-08-28 08:59:40     c:/bla.abc      1       7           1           1
19      1       BR11S140325004685   2014-08-28 10:00:00     c:/bla.abc      1       8           1           1
20      0       BR11S140325004685   2014-08-28 10:00:12     c:/bla.abc      1       8           1           1
21      1       BR11S140325004685   2014-08-28 10:01:30     c:/bla.abc      1       8           1           1
22      0       BR11S140325004685   2014-08-28 10:01:40     c:/bla.abc      1       8           1           1
23      1       BR11S140325004685   2014-08-28 10:08:40     c:/bla.abc      1       8           1           1
24      0       BR11S140325004685   2014-08-28 10:09:40     c:/bla.abc      1       8           1           1

PRODUCAO_DIARIA
===============
id  date        total_inspections   models_id   start_time  end_time
1   2014-08-27  8                   7           09:50:36    11:05:19
2   2014-08-27  5                   6           11:07:33    12:42:34
3   2014-08-28  5                   7           08:55:12    08:59:40
4   2014-08-28  6                   8           10:00:00    10:09:40

The best I could was to do a query that shows me the inspections, but it doesn’t count all the separate templates. See:

select date_inspection, models_id, count(models_id)
from 
(
    select *
    from 
    (
        select*from Inspecoes where is_ok = 0
    ) as t
    group by date_inspection
)as t
group by models_id;

But the return of this query is :

date_inspection     models_id   count(models_id)
2014-08-27 12:40:14 6           2
2014-08-27 10:41:35 7           5
2014-08-28 10:00:12 8           3

It returns the wrong counts and still lacks models.

Where am I going wrong?

mysql sql database group-by
  • 1

    For start_time and end_time use MIN and MAX respectively.

  • OK @Motta, but he takes the Min and Max gives a selection of inspections that are in trouble because I passed the filter where is_ok = 0 . Actually I need the start and end times of the INSPECTOES table before passing the filter. How can I do this in this query?

  • select date_inspection, models_id, min(date_inspection) start_time , max(date_inspection) end_time , count(models_id) 
from ( select * 
 from ( select *
 from Inspecoes 
 where is_ok = 0 ) t group by date_inspection )as t group by models_id; not taking into account the question of the filter I didn’t understand , which indicates is_ok !?

  • @Motta, thanks for the tips. I edited the question to improve understanding for you and other users as well.

1 answer

4


From what I understand, your problem is separating the records by model and by date. Your query is grouping only by template. In order for dates to be taken into account when grouping, you need to add them to group by before grouping by template.

Would look like this

SELECT
    `date_inspection` AS 'date', 
    count( `models_id` ) AS 'total_inspections'
    `models_id`,
    DATE_FORMAT( min( `date_inspection` ) ,'%H:%i:%s' ) AS 'start_time',
    DATE_FORMAT( max( `date_inspection` ) ,'%H:%i:%s' ) AS 'end_time'
FROM `Inspecoes`
GROUP BY 
    YEAR(`date`),
    MONTH(`date`),
    DAY(`date`),
    `model_id`;

The return must be as desired.

If it is important to know the number of inspections that occurred well or not, you can add a conditional counter, being as follows:

SELECT
    `date_inspection` AS 'date', 
    COUNT( `models_id` ) AS 'total_inspections'
    `models_id`,
    DATE_FORMAT( MIN( `date_inspection` ), '%H:%i:%s' ) AS 'start_time',
    DATE_FORMAT( MAX( `date_inspection` ), '%H:%i:%s' ) AS 'end_time',
    COUNT( IF( `is_ok` = "1", 1, null ) ) AS 'success', 
    COUNT( IF( `is_ok` = "0", 1, null) ) AS 'error'
FROM `Inspecoes`
GROUP BY
    YEAR( `date` ),
    MONTH( `date` ),
    DAY( `date` ),
    `model_id`;

Note: I make clear in the query what is table, field, value or keyword using quotation marks, apostrophe or capital letter. This not only helps in reading but also prevents query execution errors. I recommend starting the same.

  • Perfect @marcusagm. Exactly what I needed. Thank you.

  • How nice! The orders =]

Browser other questions tagged mysql sql database group-by

You are not signed in. Login or sign up in order to post.