2

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.  

  • Show us what you got so far. – ypercubeᵀᴹ Jun 15 '14 at 23:16
3

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

  • Then you might want to mark cha's answer as correct? It is an elegant solution! – Vérace Jul 18 '14 at 9:16
  • @cha You probably want to change the prod_date in the select list, to year(prod_date) AS prod_year, month(prod_date) AS prod_month. MySQL allows what you wrote but it's not best practice, as you'll get a semi-random result for prod_date. – ypercubeᵀᴹ Jul 18 '14 at 12:48
  • @ypercube: thanks for the suggestion. I have updated the answer. The reason I left as prod_date was that it was in the original question – cha Jul 20 '14 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.