I am back with yet another question. I am trying to pull data from a table that it populated everyday with the weight of scrap used for each shift and show it on a bar graph according to each shift. My problem is that I cannot get my query to do exactly what I want. The code below doesn’t work but it is what I theoretically want. I would like the sums to add up only the weights for a certain shift:
SELECT distinct(sb.process) as "Process",
sum(SELECT s.weight FROM scrap WHERE shift = 1) as "Shift 1",
sum(SELECT s.weight FROM scrap WHERE shift = 2) as "Shift 2",
sum(SELECT s.weight FROM scrap WHERE shift = 3) as "Shift 3"
FROM scrap_bins sb, scrap s
WHERE sb.bin_id = s.bin AND s.date
BETWEEN '{Root Container.start.date}' AND '{Root Container.finish.date}'
GROUP BY sb.process
I tried using only one sum() function and GROUP BY the shift but it did not work. I need three columns so that my bar graph will render each shift and the total weight for each. This is how my tables look:
scrap (date, bin, shift,weight)
scrap_bins (bin_id, descp, process)
I know I could make my scrap table have 3 columns for each shift and then just sum those columns but that seems like I’d be getting away from a normalized database which is something I don’t really care to do.
Also, I am using the regular “Bar Graph” tool in FactoryPMI. Should I be using the Charts Wizard? I am aware it has a lot more functionality but am unsure if thats what I should be using to get what I want.
I don’t think I understand well what your precise goal is. From reading your question, it looks like a simple GROUP BY should work. For example, with your table, the following query would produce a bar chart with 3 columns, one for each shift, and the total produced for those shifts based on the time selected:
SELECT shift, sum(weight) FROM scrap WHERE date
BETWEEN '{Root Container.start.date}' AND '{Root Container.finish.date}' GROUP BY shift
Now, I guess what I don’t get is the role of ‘process’. How does it fit in? Do you need to further break it down and get the sum per shift per bin?
Regards,
Sorry about that, I should have explained that a little more. I have around 24 bins that scrap is put into from various machines around the plant. These machines bend , cut , and shape metal parts. What the machine does is what we call the “process”. It’s for linking similar bins together so that each bin isn’t entirely specific. For example, we have 3 different punch machines but the scrap that comes off each machine needs to be rendered as scrap from a punch machine, that is why we give it a field called “process” and give it a value “punch”. Let me know if that doesn’t make sense and I need to clarify further.
Onto the matter at hand. The query you gave would work if all I was looking for was the total weight for each shift. But, as you said, I want another layer of separation. I have a total of 9 different processes and want each process to have 3 bar graphs, one bar for the total weight for each shift. Let me know what you think or if I need to clarify anything else further. Thanks for your help!
Hmm, this is a tricky one, seems like there should be something super elegant. However, I guess I’ll settle for half-elegant. I think this might work:
SELECT sb.process, sum(case when s.shift=1 then s.weight else 0 end) as 'Shift 1',
sum(case when s.shift=2 then s.weight else 0 end) as 'Shift 2',
sum(case when s.shift=3 then s.weight else 0 end) as 'Shift 3' FROM scrap s, scrap_bins sb WHERE sb.bid=s.bin AND s.date
BETWEEN '{Root Container.start.date}' AND '{Root Container.finish.date}' GROUP BY sb.process;
Seemed to produce the correct results on my meager set of test data that I threw together. Let me know how it goes…
That worked great! I was thinking more along the lines of some weird nested query but this works just fine. I was unaware you could use that syntax inside of the sum function. Can you also use that with any aggregate function?
Cool, glad it worked. Yeah, I imagine you can do most things inside the aggregate functions… they’re defined in the MySQL manual like SUM(expr) and expr usually covers most statements (sorry, I tried to find an exact definition on the mysql website but for some reason couldn’t).
Regards,