RE: Sum problem....

[Jay Blanchard]

[snip]
No, they have different record numbers...
[/snip]


a. Always reply to the list. I just happened to spot this in my spam
folder while cleaning.

2. You're getting Cartesian results, so your query needs to be a little
more specific

Your original query --

select (sum(table1.qty) + sum(table2.qty)) as val,name from
table1,table2
group by name order by val desc

try this (not tested)

SELECT (SUM(table1.qty) + SUM(table2.qty)) as val, table1.name
FROM table1 LEFT OUTER JOIN table2
ON(table1.identifier = table2.identifier)
GROUP BY table1.name
ORDER BY val DESC

You may have to do 2 queries with

(1)WHERE table2.identifier IS NULL  //or (2)IS NOT NULL

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Sum problem....

[Ian Sales]

Mike Morton wrote:
I have 2 tables, identical in fields.  One is an 'archived items' table, the
other is a live items.
I want to run a query that will grab the sum of all products in both tables,
like:
The results would display the total qty by item:
QTY NAME
The logic is something like:
sum(table1.qty) + sum(table2.qty)
The query that I came up with is:
select (sum(table1.qty) + sum(table2.qty)) as val,name from table1,table2
group by name order by val desc
It is just a bit off... (like 3 off the actual count :) )
I have tried to do it with a where and group by clause, but no luck there
either...
Any suggestions?
 

- you've got a Cartesian results set because you haven't joined the 
tables. Try joining on name, or writing two queries and then using a UNION.

- ian
--
+---+
| Ian Sales  Database Administrator |
|   |
| eBuyer  http://www.ebuyer.com |
+---+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Sum problem....

[Jay Blanchard]

[snip]  
I have 2 tables, identical in fields.  One is an 'archived items' table,
the
other is a live items.
[/snip]

Are they also identical in record count?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Sum problem....

[Mike Morton]

I have 2 tables, identical in fields.  One is an 'archived items' table, the
other is a live items.

I want to run a query that will grab the sum of all products in both tables,
like:

The results would display the total qty by item:

QTY NAME

The logic is something like:

sum(table1.qty) + sum(table2.qty)

The query that I came up with is:

select (sum(table1.qty) + sum(table2.qty)) as val,name from table1,table2
group by name order by val desc


It is just a bit off... (like 3 off the actual count :) )

I have tried to do it with a where and group by clause, but no luck there
either...

Any suggestions?

TIA!

--
Cheers

Mike Morton


*
* Tel: 905-465-1263
* Email: [EMAIL PROTECTED]
*


"Indeed, it would not be an exaggeration to describe the history of the
computer industry for the past decade as a massive effort to keep up with
Apple."
- Byte Magazine

Given infinite time, 100 monkeys could type out the complete works of
Shakespeare. Win 98 source code? Eight monkeys, five minutes.
-- NullGrey 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: multitable sum problem

[Leo]

i think you should have a sales person table
that hold unique id for each sales force
such as

salesrep (id,etc..)
| 101 | ... |
| 102 | ... |

so you can alter the query into 
select 
salesrep.id,
sum(ifnull(salesinvoices.invamt,0)) as curramt,
sum(ifnull(lysalesinvoices.lyinvamt,0)) as lyamt
from 
salesrep
left join salesinvoices on salesrep.id=salesinvoices.salesrepid
left join lysalesinvoices on salesrep.id=lysalesinvoices.salesrepid
group by salesrep.id

hope this help..

-Leo-

multitable sum problem

[David Katz]

I have the following two tables:

salesinvoiceslysalesinvoices
  salesrepidsalesrepid  
  invamt  lyinvamt

I am trying to get a current year and last year sum for each sales rep.  The problem 
is it looks like it is resuming each file for each record.  
select salesrepid, sum(invamt) as curramt, sum(lyinvamt) as lyamt from 
salesinvoices,lysalesinvoices group by salesrepid order by curramt;

++-+--+
| salesrepid | curramt| lyamt  |
+---+---+-+
| 101   | 3434464.00  | 4286744.00 |
| 102   | 3507312.00  | 4286744.00 |
+---+---+--+

I've attached the individual file records below.

Any help would be appreciated.

Thanks
David.


mysql> select salesrepid, invamt from salesinvoices;
++--+
| salesrepid | invamt   |
++--+
| 101| 25230.00 |
| 101| 34332.00 |
| 101| 24564.00 |
| 101| 15155.00 |
| 101| 43243.00 |
| 101| 34352.00 |
| 101| 24352.00 |
| 101| 13426.00 |
| 102| 26330.00 |
| 102| 36433.00 |
| 102| 25436.00 |
| 102| 16335.00 |
| 102| 43543.00 |
| 102| 34456.00 |
| 102| 23422.00 |
| 102| 13252.00 |
++--+

mysql> select lysalesrepid, lyinvamt from lysalesinvoices;
+--+--+
| lysalesrepid | lyinvamt |
+--+--+
| 101  | 25230.00 |
| 101  | 34332.00 |
| 101  | 24564.00 |
| 101  | 15155.00 |
| 101  | 32650.00 |
| 101  | 43224.00 |
| 101  | 43623.00 |
| 101  | 45633.00 |
| 102  | 23330.00 |
| 102  | 34653.00 |
| 102  | 23264.00 |
| 102  | 26435.00 |
| 102  | 43550.00 |
| 102  | 32524.00 |
| 102  | 32353.00 |
| 102  | 55323.00 |
+--+--+