CODEWITHSUNDEEP
mysql
mpen
mpen

Reputation: 283003

How to get counts from two separate tables based on time?

I have four tables, Companies, Vehicles, Quotes, and PhoneRequests.

Companies may receive both Quotes and Requests on their Vehicles.

Each Quote and Request is stamped with a time.

I want to find the number of Quotes and Requests each company has received during each month as long as either the number of Quotes or Requests is greater than 0.

I don't know how to group such that the month from two separate tables is the same.

Here's what I've got so far:

SELECT
    company.name `company`,
    vehicle.name `vehicle`,
    DATE_FORMAT(COALESCE(quote.qtime, pr.date),'%Y-%m') `month`,
    COUNT(DISTINCT quote.qid) `quotes`,
    COUNT(DISTINCT pr.id) `phone_reqs`
FROM companies company
    LEFT JOIN vehicles vehicle ON vehicle.compid=company.cid
    LEFT JOIN quote_vehicle qv ON qv.vehicle_id=vehicle.vid
    LEFT JOIN quotes quote ON qv.quote_id=quote.qid
    LEFT JOIN phone_requests pr ON pr.vehicle_id=vehicle.vid AND quote.qtime IS NULL OR DATE_FORMAT(quote.qtime,'%Y-%m')=DATE_FORMAT(pr.date,'%Y-%m')
GROUP BY
    company.cid, `month`
HAVING
    quotes > 0 OR phone_reqs > 0
ORDER BY
    `month` DESC, `company` ASC

But it seems to be attributing the phone_reqs to the wrong company, probably because of that AND quote.qtime IS NULL OR DATE_FORMAT(quote.qtime,'%Y-%m')=DATE_FORMAT(pr.date,'%Y-%m') bit. I don't know how else to ensure the Quote and Request occurred during the same month though.

Upvotes: 0

Views: 79

Answers (3)

jerdiggity
jerdiggity

Reputation: 3665

You said

... probably because of that AND quote.qtime IS NULL OR DATE_FORMAT(quote.qtime,'%Y-%m')=DATE_FORMAT(pr.date,'%Y-%m') bit.

What about adding a simple WHERE clause?

SELECT
    company.name `company`,
    vehicle.name `vehicle`,
    DATE_FORMAT(COALESCE(quote.qtime, pr.date),'%Y-%m') `month`,
    COUNT(DISTINCT quote.qid) `quotes`,
    COUNT(DISTINCT pr.id) `phone_reqs`
FROM companies company
    LEFT JOIN vehicles vehicle ON vehicle.compid=company.cid
    LEFT JOIN quote_vehicle qv ON qv.vehicle_id=vehicle.vid
    LEFT JOIN quotes quote ON qv.quote_id=quote.qid
    LEFT JOIN phone_requests pr ON pr.vehicle_id=vehicle.vid
WHERE
  (quote.qtime IS NULL) OR (quote.qtime BETWEEN pr.date AND pr.date - 2592000)
GROUP BY
    company.cid, `month`
HAVING
    quotes > 0 OR phone_reqs > 0
ORDER BY
    `month` DESC, `company` ASC

NOTE: 2592000 represents 30 days (assuming your dates are timestamps)...

Upvotes: 1

mpen
mpen

Reputation: 283003

Figured something out:

SELECT `month`, `company`, SUM(nquotes) nquotes, SUM(nphone) nphone  FROM ((
    SELECT
        DATE_FORMAT(quote.qtime,'%Y-%m') `month`,
        company.name `company`,
        COUNT(*) `nquotes`,
        0 `nphone`,
        company.cid `company_id`
    FROM
        companies company
        JOIN vehicles vehicle ON vehicle.compid=company.cid
        JOIN quote_vehicle qv ON qv.vehicle_id=vehicle.vid
        JOIN quotes quote ON qv.quote_id=quote.qid
    GROUP BY `month`, `company_id`
) UNION ALL (
    SELECT
        DATE_FORMAT(pr.date,'%Y-%m') `month`,
        company.name `company`,
        0 `nquotes`,
        COUNT(*) `nphone`,
        company.cid `company_id`
    FROM
        companies company
        JOIN vehicles vehicle ON vehicle.compid=company.cid
        JOIN phone_requests pr ON pr.vehicle_id=vehicle.vid
    GROUP BY `month`, `company_id`
)) master
GROUP BY `month`, `company_id`
ORDER BY `month` DESC, `company` ASC

Upvotes: 0

Dharmesh Patel
Dharmesh Patel

Reputation: 1891

May be this can help

SELECT
    company.name `company`,
    vehicle.name `vehicle`,
    month,
    quote.quotes,
    pr.phone_reqs
FROM companies company
    LEFT JOIN vehicles vehicle ON vehicle.compid=company.cid
    LEFT JOIN quote_vehicle qv ON qv.vehicle_id=vehicle.vid
LEFT JOIN (SELECT COUNT(DISTINCT qid) quotes, DATE_FORMAT(qtime,'%Y-%m') `month` FROM quotes GROUP BY `month`) quote ON quote.qid = qv.quote_id
LEFT JOIN (SELECT COUNT(DISTINCT id) `phone_reqs`, DATE_FORMAT(date,'%Y-%m') `month` FROM phone_requests GROUP BY `month`) pr ON pr.phone_reqs = vehicle.vid AND quote.month = pr.month
GROUP BY  company.cid
HAVING
    quotes > 0 OR phone_reqs > 0
ORDER BY
    `month` DESC, `company` ASC

Upvotes: 1

Related Questions