SQL Group query with more than one distinguished attribute

Question

SQL Group query with more than one distinguished attribute

Navigation

#1 by (4 votes)

3

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

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

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

Thinking about eliminating the fact of generating the large volume of id's generated per day, at the end of each day I could read this INSPECOES table, record those that were disapproved in a table (INSPECOES_REPROVADAS), record the daily production data in another table (PRODUCAO_DIARIA), delete all records in the INSPECOES table, and finally restart the ID's counter, thus preparing the table to start a new inspection record with the 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 not add inspections of different models to the same given.

See 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 most I could do was to do a query that shows me the inspections, but it does not count all the separate models. 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

She returns the wrong counts and models are missing.

Where am I going wrong?

mysql sql group-by

asked by anonymous 30.08.2014 / 22:47

1 answer

Connection MySQL Local Database and Online Server How to create an emulator without using ide?

score 4 · Accepted Answer

As far as I understand, your problem is separating records by model and by date. Your query is grouping by model only. For dates to be taken into account when grouping, you need to add them to group by before grouping by model.

It 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';

Return should be as desired.

If it is important to know the number of inspections that occurred well or not, you can add a conditional counter, 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: Note that I make it clear in the query what a table, field, value, or keyword is by using quotation marks, apostrophe, or uppercase. This not only helps in reading but avoids query execution errors. I recommend starting to do the same.