CODEWITHSUNDEEP
mysqlsqlcountaggregate
Alec Sanger
Alec Sanger

Reputation: 4562

Counting the number min records within groups

I'm building a report for a database where I need to determine the number of "first scans" grouping by company, job, and date.

The scan table can contain multiple scans for the same item, however I only want to include the original scan in my COUNT, which can only be identified as being the scan with the earliest date that matches a particular item.

My first attempt at this was:

SELECT 
     _item_detail.job_id, 
     _item_group.group_id, 
     _scan.company_id, 
     DATE(scan_date_time) as scan_date, 
     COUNT(1)
FROM _scan
    INNER JOIN _item_detail ON _item_detail.company_id = _scan.company_id 
    AND 
    _item_detail.serial_number = _scan.serial_number
    INNER JOIN _item_group ON _item_group.group_id = _item_detail.group_id
WHERE _item_detail.job_id = '0326FCM' AND _scan.company_id = '152345' AND _item_group.group_id = 13
GROUP BY 
_item_detail.job_id, 
_item_group.group_id, 
_scan.company_id, scan_date -- first_scan_count
HAVING min(scan_date_time);

This is giving me incorrect results, though (about 3x too many). I am assuming it's because the MIN record is being recalculated for each date, so if the min was found on day 1, it may also be found on day 3 and counted again.

How can I modify my query to achieve the desired results?

Upvotes: 0

Views: 76

Answers (2)

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

Something similar to this should work... I'm not completely sure of how your tables are laid out or how the data relates them together, but this is the general idea:

SELECT 
    _item_detail.job_id, 
    _item_group.group_id, 
    _scan.company_id, 
    DATE(scan_date_time) as scan_date, 
    COUNT(1)
FROM
    _scan s1
    INNER JOIN _item_detail 
        ON _item_detail.company_id = s1.company_id 
        AND _item_detail.serial_number = s1.serial_number
        AND _item_detail.job_id = '0326FCM'
    INNER JOIN _item_group 
        ON _item_group.group_id = _item_detail.group_id
        AND _item_group.group_id = 13
WHERE 
    s1.company_id = '152345'
    AND s1.scan_date_time = (
        SELECT MIN(s2.scan_date_time)
        FROM _scan s2
        WHERE 
            s2.company_id = s1.company_id
            AND s2.serial_number = s1.serial_number
    )
GROUP BY 
    _item_detail.job_id, 
    _item_group.group_id, 
    s1.company_id

Upvotes: 2

Aerik
Aerik

Reputation: 2317

I don't quite follow your query, but based on the description of the problem, I'd say create a subquery that gives the min scan date for for each item, group by items, the perform your outer select on that.

Upvotes: 0

Related Questions