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.