Totals from query

[muppet]

I have a table with information about the type of scrap generated on a bag making machine. Every time scrap in generated on either side of the machine (left or right, there are 2 packing machines on the one bag maker) a line is added to the table with the following information,
scrap_left, scrap_right, reason_left, reason_right

So if the left side found 2 bags that had dirt, a line might read like this,

| scrap_left | scrap_right | reason_left | reason_right |
|      2     |             |    dirt     |              |

After a shift there are 20 - 30 lines with up to 20 different reason like so,

| scrap_left | scrap_right | reason_left | reason_right |
|      2     |             |    dirt     |              |
|            |       4     |             |   torn       |
|            |       1     |             |   line up    |
|            |       2     |             |   torn       |
|      5     |             |  wide ties  |              |

I am generating a report in Excel using VB to perform the querys and its all working well.

But I want to generate a table with the totals like so

| Reason      | Quantity |
| Torn        |    6     |
| wide ties   |    5     |
| dirt        |    2     |
| line up     |    1     |

Can I do this with a single query in MySQL or should I be doing it in VB?

[tesuji]

These union selects

select reason_right as "Reason", sum(scrap_right) as "Quantity" from bakma_table
  where "Reason" IS NOT NULL group by "Reason"
union
select reason_left as "Reason", sum(scrap_left) as "Quantity" from bakma_table
  where "Reason" IS NOT NULL group by "Reason"
order by "Quantity" desc ;

should give such a list

| Reason      | Quantity |
| Torn        |    6     |
| wide ties   |    5     |
| dirt        |    2     |
| line up     |    1     |

Quotation marks (" ") could be omitted, sometimes.

This code not tested, thought.

-- tesu

[muppet]

Works perfect! Thanks for the solution.

[muppet]

After missing it during the whole testing phase, a problem was pointed out to me my a user.

I am using this query that tesuji provided:

select reason_right as "Reason", sum(scrap_right) as "Quantity" from bakma_table
  where "Reason" IS NOT NULL group by "Reason"
union
select reason_left as "Reason", sum(scrap_left) as "Quantity" from bakma_table
  where "Reason" IS NOT NULL group by "Reason"
order by "Quantity" desc ;

This works well but it does not combine the 2 queries together properly. By them self they both work well, but once 'unioned' together they are not accurate.

When 'unioned' the first query works as it is supposed to, but the second returns the TOTAL quantity in a single entry and adds it as an additional entry. so I am left with something like:

| Reason      | Quantity |
| wide ties   |    5     |
| dirt        |    2     |
| line up     |    7     |

With 'line up' being the sum of the second query in a single entry.

I should clarify the problem. The following is a table generated by a machine:

| scrap_left | scrap_right | reason_left | reason_right |
|      2     |             |    dirt     |              |
|            |       4     |             |   torn       |
|            |       1     |             |   line up    |
|            |       2     |             |   torn       |
|      5     |             |  wide ties  |              |
|      1     |             |  wide ties  |              |
|            |       1     |             |   wide ties  |

And this is the desired outcome:

| Reason      | Quantity |
| Torn        |    6     |
| wide ties   |    7     |
| dirt        |    2     |
| line up     |    1     |

[tesuji]

Hi muppet,

sorry for the problems arose on your side through my simple solution. Indeed, I didn't consider that the same reason could appear on both sides. So, if you apply my old union select

select 'right' as Side, reason_right as "Reason", sum( scrap_right) as "Quantity" from bakma
  where "Reason" IS NOT NULL group by "Reason" 
union
select 'left' as Side, reason_left as "Reason", sum(scrap_left) as "Quantity" from bakma
  where "Reason" IS NOT NULL group by "Reason"
order by "Quantity" desc

on the new sample data which seems to be the old one extended by two further rows so as to reveal that weak select, the result is:

/*
Side   Reason    Quantity
-------------------------
right  torn      6
left   wide ties 6
left   dirt      2
right  wide ties 1
right  line up   1
*/

To show which side the rows are from, I added text 'left'/'right'. So it's clear reason 'wide ties' must appear twice. This will also occasionally happen for the other reasons.

Nevertheless, further aggregation is simple if above select is put in SQL-WITH statement, also supported by mysql, to further sum up identical rows:

WITH totaltotal (Side, Reason, Quantity) as
(
  select 'right' as Side, reason_right as "Reason", sum( scrap_right) as "Quantity" from bakma
    where "Reason" IS NOT NULL group by "Reason" 
  union
  select 'left' as Side, reason_left as "Reason", sum(scrap_left) as "Quantity" from bakma
    where "Reason" IS NOT NULL group by "Reason"
  order by "Quantity" desc
)
select Reason, sum(Quantity) as "Total Quantity" from totaltotal group by Reason order by "Total Quantity" desc;

/*
The result should be now:

Reason    Total-Quantity
------------------------
wide ties 7
torn      6
dirt      2
line up   1
*/

I hope this clears up some things (also that result where innocent 'line up' is blamed to be 7. Your explanation for that impossibility sounds nicely).

-- tesu

[muppet]

Tesu,

thanks for the quick response. I haven't seen the WITH command before and my MySQL spits out this error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WITH totaltotal (Side, Reason, Quantity) as
(
  select 'right' as Side, reason' at line 1

From this query

WITH totaltotal (Side, Reason, Quantity) as
(
  select 'right' as Side, reason_r as "Reason", sum( waste_r) as "Quantity" from abm_status
    where "Reason" IS NOT NULL group by "Reason"
  union
  select 'left' as Side, reason_l as "Reason", sum(waste_l) as "Quantity" from abm_status
    where "Reason" IS NOT NULL group by "Reason"
  order by "Quantity" desc
)
select Reason, sum(Quantity) as "Total Quantity" from totaltotal group by Reason order by "Total Quantity" desc;

I'm on a windows machine, if that makes a difference.

[tesuji]

Ah muppet, too bad that mysql still don't support ANSI SQL WITH-clause which has been introduced since SQL 1999 standard.

Yet there are some other approaches to solve it even on mysql.

So next try:

select Reason, sum(Quantity) as "Total Quantity" from
(
  select 'right' as Side, reason_right as "Reason", sum( scrap_right) as "Quantity" from bakma2
    where "Reason" IS NOT NULL group by "Reason" 
  union
  select 'left' as Side, reason_left as "Reason", sum(scrap_left) as "Quantity" from bakma2
    where "Reason" IS NOT NULL group by "Reason"
) as musthavename
group by Reason order by "Total Quantity" desc;

Should function on mysql for msyql manual tells that subqueries in from clause would be fully supported now. I am about to believe that :D

Result might be as desired:

| Reason      | Quantity |
| wide ties   |    7     |
| Torn        |    6     |
| dirt        |    2     |
| line up     |    1     |

-- tesu

[tesuji]

Don't worry there are two further tries to go which I know from (or feel so at least)

[muppet]

tesuji,

OK, I ran with your suggested code

select Reason, sum(Quantity) as "Total Quantity" from
(
  select 'right' as Side, reason_r as "Reason", sum( waste_r) as "Quantity" from abm_status
    where "Reason" IS NOT NULL group by "Reason"
  union
  select 'left' as Side, reason_l as "Reason", sum(waste_l) as "Quantity" from abm_status
    where "Reason" IS NOT NULL group by "Reason"
) as musthavename
group by Reason order by "Total Quantity" desc;

and got the result

'', 403261

So no good here. I"m glad you have 2 more possible solutions up your sleeve.

[tesuji]

well muppet,

yes, there are other solutions up the sleeve, for example a permanent view (for mysql's unability processing standard sql with clause) or simply a stored procedure which returns the union-part of the censured query.

However such stuff wouldn't have been necessary if one had simply replaced ansi sql standard delimeter " " by mysql's propritary well-known ` ` ones! You may study attached screen shot below.

I wish you all the very best!

-- tesu

[muppet]

Ahh, a long headache because of a " instead of a ' !!!

Your code works well, a small hang up that I can live with is it returns a number at the top of Total Quantity with a blank Reason. I'm importing this into excel, so I'll just blank out that top row after this code runs.

Thank you very much for your time tesuji! You have saved this project from derailment. I will put forward 'Tesuji' as the name of our next born son in your honour.

[tesuji]

>>> You have saved this project from derailment. I will put forward 'Tesuji' as the name of our next born son in your honour.

Haha, I cherish you, yet you should grasp tesuji´s meaning above all. (Indeed, it has really good meaning in Japanese once I accidently came across with due to the initials of my names Te. Su.)

My problem with mysql is I actually almost never use it seriously, except for customers asking to getting some help if their badly designed applications, what's typically inherent in the mysql system, let programming costs explode.

tesu