| question regarding group by ..... [message #374619] |
Thu, 21 June 2001 13:31  |
JXM
Messages: 6
Registered: June 2001
|
Junior Member |
|
|
I want to add two parts from another table to the following:
select A.upc, SUM(A.quantity), Sum(A.amount)
from log1 A, item B
where A.upc = B.upc
group by A.upc
UNION All
select C.upc, SUM(C.quantity), Sum(C.amount)
from log2 C, item D
where C.upc = D.upc
group by C.upc
This will produce columns like
upc | sum (quantity) | sum (amount)
with entries ...
What I want to do is add two more columns to the end of the print out to look like
upc | sum (quantity) | sum (amount) | sprice | type
The last two(sprice, item) would come from the table item. The UPC is the key for the item table.
How do I write that in sql?
|
|
|
|
| Re: question regarding group by ..... [message #374622 is a reply to message #374619] |
Thu, 21 June 2001 14:39  |
sandeep
Messages: 110
Registered: October 2000
|
Senior Member |
|
|
try this :
select A.upc,SUM(A.quantity),Sum(A.amount),B.sprice,B.type
from log1 A, item B
where A.upc = B.upc
group by A.upc,
B.sprice,
B.type
UNION All
select C.upc,SUM(C.quantity),Sum(C.amount),D.sprice,D.type
from log2 C, item D
where C.upc = D.upc
group by C.upc,
D.sprice,
D.type
did it work ?
|
|
|
|
| Re: question regarding group by ..... [message #374649 is a reply to message #374619] |
Fri, 22 June 2001 14:34 |
JXM
Messages: 6
Registered: June 2001
|
Junior Member |
|
|
Well it sort of did. I chaged the problem to an easier one (perhaps). I stuck all the various values into a single table called temp.
Now I get this error statement saying that this is not a valid group function. Any ideas why?
SELECT A.upc, SUM(A.quantity), SUM(A.total_price), A.category, A.amount
FROM temp A, item B
WHERE A.upc = B.upc
group by A.upc;
I just want to be able to sort out the table by category and upc, and take the sum of everything with the same upc for the quantity and total_price fields.
Any ideas?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
