[thelist] IMPORTANT UPDATE MySQL to Excel Problem Pivot Table SOLVED! [long]

Jay Blanchard jay.blanchard at niicommunications.com
Thu May 2 13:34:01 CDT 2002


Howdy gurus,

I should be hanging my head because I was egotistical enough to think myself
like rudy yesterday with the crosstab query for a pivot table. While the
query worked it was <em>not quite accurate</em>. Here was the problem, the
IF statement would only return one value per row as written

tbleFOOBAR
+----------+----------------+
| RecordID | Weight         |
+----------+----------------+
| Foo      | 1.0            |
| Bar      | 2.5            |
| Cake     | 14.0           |
| Syrup    | 0.3            |
+----------+----------------+

SELECT RecordDate
IF(RecordID = 'Foo', sum(*), 0) AS Foo,
IF(RecordID = 'Bar', sum(*), 0) AS Bar,
IF(RecordID = 'Cake', sum(*), 0) AS Cake,
IF(RecordID = 'Syrup', sum(*), 0) AS Syrup
from tblFOOBAR
GROUP BY RecordDate;

would return;
+----------+----------+----------+----------+---------+
|RecordDate| Foo      | Bar      | Cake     | Syrup   |
+----------+----------+----------+----------+---------+
| 2/1/2002 | 1001     | 0        | 0        | 0       |
| 2/2/2002 | 13201    | 0        | 0        | 0       |
| 2/3/2002 | 0        | 125      | 0        | 0       |
| 2/4/2002 | 1106     | 0        | 0        | 0       |
| 2/5/2002 | 0        | 0        | 0        | 14      |
+----------+----------+----------+----------+---------+

This was noted after we looked over the results of the query and saw that
the numbers did not match properly with previously accurate numbers. Anytime
a column returned a positive number all columns (before &&) after it had
zero values.  That ain't right. To remedy the situation here is what must be
done;

SELECT RecordDate
SUM(IF(RecordID = 'Foo', 1, 0)) AS Foo,
SUM(IF(RecordID = 'Bar', 1, 0)) AS Bar,
SUM(IF(RecordID = 'Cake', 1, 0)) AS Cake,
SUM(IF(RecordID = 'Syrup', 1, 0)) AS Syrup
from tblFOOBAR
GROUP BY RecordDate;

will return;
+----------+----------+----------+----------+---------+
|RecordDate| Foo      | Bar      | Cake     | Syrup   |
+----------+----------+----------+----------+---------+
| 2/1/2002 | 1001     | 12       | 0        | 183     |
| 2/2/2002 | 13201    | 0        | 0        | 0       |
| 2/3/2002 | 0        | 125      | 3209     | 12      |
| 2/4/2002 | 1106     | 0        | 0        | 0       |
| 2/5/2002 | 14       | 378      | 1        | 14      |
+----------+----------+----------+----------+---------+

which is correct. Summing inside the if statement returns a total sum for
the RecordID only if no positive number has come before it from
left-to-right in the columns. It also does not return an accurate number (I
am not sure what number it does return, because I did not take the time to
add them when the error was discovered).

The IF statement nested within the SUM statement works well, and is quite
flexible. For instance, to get the quantity of an IF which is true; [ IF
syntax in SQL is IF(Condition, True, Else False) ]

SUM(IF(This Is True, Add This Up, Add This Up If False))
So, SUM(IF(RecordID = 'Foo', 1, 0)) is essentially summing the "1's"
together where the RecordID is TRUE. And summing 0 if things are false
returns a 0.

Here is where the flexibility comes into this, lets say that each item has a
measurement associated with it other than quantity, say weight(in pounds)
per item. I need to know how many pounds of each item I shipped between
2/1/02 and 2/5/02 because the guys who want to race the big brown truck are
trying to say what I shipped and what they carried is different. Using the
same conditional statement I can sum each weight;

SUM(IF(RecordID = 'Foo', Weight, 0)) AS FooWeight

SUM will total up either the TRUE or FALSE condition. If you want to get
really complicated from here, you can nest IF statements. But since my head
hurts I may reserve it for an article. Ah the power of SQL. rudy, you may
obviously keep your sceptre, I am in no position to be a prince, much less a
king.

Jay






More information about the thelist mailing list