Why? Because it's Friday and I'm feeling silly :)
mysql> SELECT * FROM sales;
+--------------+-------+--------+
| company | state | sales |
+--------------+-------+--------+
| ABC | AZ | 140.01 |
| XYZ | AZ | 17.76 |
| ABC | NY | 123.45 |
| XYZ | NY | 123.00 |
| Widgets Inc. | NY | 45.09 |
| ABC | NY | 23.73 |
| Widgets Inc. | CA | 97.30 |
+--------------+-------+--------+
7 rows in set (0.00 sec)
mysql> SELECT 'each' AS 'By', company AS Company, state AS State, LPAD(
SUM(sales), 9, ' ' ) AS Sales,
-> LPAD( FORMAT( SUM(sales)*100/(SELECT SUM(sales) FROM sales),2), 9, ' '
) AS Percent,
-> REPEAT( '*', SUM(sales)*50/(SELECT SUM(sales) FROM sales) )
-> AS Graph
-> FROM sales
-> GROUP BY company, state
-> UNION
-> SELECT ' company', company, '', LPAD( SUM(sales), 9, ' ' ),
-> LPAD( FORMAT( SUM(sales)*100/(SELECT SUM(sales) FROM sales),2), 9, ' '
),
-> REPEAT( '*', SUM(sales)*50/(SELECT SUM(sales) FROM sales) )
-> FROM sales
-> GROUP BY company
-> UNION
-> SELECT ' state', '', state, LPAD( SUM(sales), 9, ' ' ),
-> LPAD( FORMAT( SUM(sales)*100/(SELECT SUM(sales) FROM sales),2), 9, ' '
),
-> REPEAT( '*', SUM(sales)*50/(SELECT SUM(sales) FROM sales) )
-> FROM sales
-> GROUP BY state
-> UNION
-> SELECT ' -----', '-----', '-----', REPEAT( '-', 9 ), REPEAT( '-', 9 ),
REPEAT( ' ', 50 )
-> UNION
-> SELECT '-----', '-----', '-----', REPEAT( '-', 9 ), REPEAT( '-', 9 ),
REPEAT( ' ', 50 )
-> ORDER BY 1,2
-> ;
+-----------+--------------+-------+-----------+-----------+----------------------------------------------------+
| By | Company | State | Sales | Percent | Graph
|
+-----------+--------------+-------+-----------+-----------+----------------------------------------------------+
| company | ABC | | 287.19 | 50.35 |
************************* |
| company | Widgets Inc. | | 142.39 | 24.97 | ************
|
| company | XYZ | | 140.76 | 24.68 | ************
|
| ----- | ----- | ----- | --------- | --------- |
|
| state | | AZ | 157.77 | 27.66 | **************
|
| state | | CA | 97.30 | 17.06 | *********
|
| state | | NY | 315.27 | 55.28 |
**************************** |
| ----- | ----- | ----- | --------- | --------- |
|
| each | ABC | AZ | 140.01 | 24.55 | ************
|
| each | ABC | NY | 147.18 | 25.81 | *************
|
| each | Widgets Inc. | CA | 97.30 | 17.06 | *********
|
| each | Widgets Inc. | NY | 45.09 | 7.91 | ****
|
| each | XYZ | AZ | 17.76 | 3.11 | **
|
| each | XYZ | NY | 123.00 | 21.57 | ***********
|
+-----------+--------------+-------+-----------+-----------+----------------------------------------------------+
14 rows in set, 2 warnings (0.01 sec)
mysql>
--
Just my 0.00000002 million dollars worth,
Shawn
"Where there's duct tape, there's hope."
"Perl is the duct tape of the Internet."
Hassan Schroeder, Sun's first webmaster
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
