On 25-Mar-2003 Black, Kelly W [PCS] wrote:
> Hello.
>
> Sorry for the long post. This has been a big problem for me, and I
> hope someone will help...
>
> I have a long standing problem with an MySQL query.
> Or perhaps a couple of sql query problems...
>
> Firstly I have been told I cannot use sum() on a value while using
> group by "parm" because it's not accurate. This is obvious as the
> result sets are often quite wrong...
>
I'm not sure where you got that. I've never seen it.
mysql> select sum(bonus) from webmaster;
+------------+
| sum(bonus) |
+------------+
| 25406.00 |
+------------+
1 row in set (0.06 sec)
mysql> select pcode,sum(bonus),count(*) as cnt from webmaster group by
pcode;
+-------+------------+------+
| pcode | sum(bonus) | cnt |
+-------+------------+------+
| C | 104.00 | 548 |
| P | 0.00 | 18 |
| R | 500.00 | 1136 |
| S | 24802.00 | 4940 |
+-------+------------+------+
4 rows in set (0.07 sec)
mysql> select 24802.00 + 500.00 + 104.00;
+----------------------------+
| 24802.00 + 500.00 + 104.00 |
+----------------------------+
| 25406.00 |
+----------------------------+
1 row in set (0.00 sec)
> However, while attempting to use MIN(), MAX(), SUM() etc.. it will error
> saying:
>
> ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no
> GROUP
> columns is illegal if there is no GROUP BY clause
>
How about some examples?
> How can I organize data and get accurate sums? It seems the SQL crowd
> wants
> to have it's cake and eat it too on this one :)
>
> Secondly I have duplicate results. I know this to be the case for many
> reasons.
> I have manually taken the raw dump (input data file) and manually
> calculated
> all
> the results on paper, and when my counts are off, the are *always* to
> factors of
> the original number. For example if I get a result of 1024 for a certain
> count, and the
> output result is wrong, it's always some multiple of 1024 such as 2048.
>
> *However* SELECT DISTINCT doesn't SEE the duplicates, as so:
>
> mysql> CREATE TABLE clean_pcf AS SELECT DISTINCT * FROM pcf where release
> =
> curdate()-1;
> Query OK, 2438 rows affected (0.11 sec)
> Records: 2438 Duplicates: 0 Warnings: 0
>
> mysql>
>
> Any ideas on how I can get rid of the bug wars???????
>
Again. How about some examples?
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
