How do I write a mysql query to combine data from related tables into a single summary?

Question

I'm having trouble defining the data in a query for a chart. I want to summarize data from the first table and combine it with a data summary from the second table.

Products   - (serial_number, date) 
Warranties - (id, lookup_to_serial_number, warranty_issue) 

What I want to do is to get the total number of products grouped by month and the total number of Warranties grouped together by month of production. It is a one product to many Warranties relationship. I can figure out how to get one or the other, but not both. The product serial number is unique but can have many Warranties on a single product.

The chart will be total products produced in a month compared to total number of Warranties on the products produced in that month. Both totals will be plotted on the same chart.

This is what I have so far :

select count(warr_id), prod_date
from product_t, warr_t
where prod_date is not null and serial_number = product_look
group by year(prod_date), month(prod_date)

union

select count(serial_number), prod_date
from product_t
where prod_date is not null
group by year(prod_date), month(prod_date)

  This returns the results I'm looking for, but is there a way to differentiate between the two sets of data? I just came up with this an hour ago.  

Answer 1

Yes, there is a way. Just add an identifier column to the selection list. E.g.:

select count(warr_id), year(prod_date) AS prod_year, month(prod_date) AS prod_month, 'W' as type
from product_t, warr_t
where prod_date is not null and serial_number = product_look
group by year(prod_date), month(prod_date)

union

select count(serial_number), year(prod_date) AS prod_year, month(prod_date) AS prod_month, 'P' as type
from product_t
where prod_date is not null
group by year(prod_date), month(prod_date)

When you build the chart for the query use the type column for different series