sum from two tables
-
Hey there, I'm trying to use sum from two tables, but when I get the result its multiplying the sum by two for some reason. This is my query: select sum(credit.amount),sum(payment.amount) from payment, credit where cust_id=9. This is returning the sums but multiplied twice, any help appreciated, abu
-
Hey there, I'm trying to use sum from two tables, but when I get the result its multiplying the sum by two for some reason. This is my query: select sum(credit.amount),sum(payment.amount) from payment, credit where cust_id=9. This is returning the sums but multiplied twice, any help appreciated, abu
Because you use sum from two tables, the returning was caculated twice. Try the following code: select sum(credit.amount) from credit where cust_id=9 select sum(payment.amount) from payment where cust_id=9
April Comm100 - Leading Live Chat Software Provider
-
Hey there, I'm trying to use sum from two tables, but when I get the result its multiplying the sum by two for some reason. This is my query: select sum(credit.amount),sum(payment.amount) from payment, credit where cust_id=9. This is returning the sums but multiplied twice, any help appreciated, abu
-
Hey there, I'm trying to use sum from two tables, but when I get the result its multiplying the sum by two for some reason. This is my query: select sum(credit.amount),sum(payment.amount) from payment, credit where cust_id=9. This is returning the sums but multiplied twice, any help appreciated, abu
Try this:
select sum(credit.amount) credit_amount,0 payment_amount from credit where cust_id=9
UNION
select 0 credit_amount, sum(payment.amount) payment_amount from payment where cust_id=9Try Another one:
Select * from amounts
(
select sum(credit.amount) credit_amount,0 payment_amount from credit where cust_id=9
UNION
select 0 credit_amount, sum(payment.amount) payment_amount from payment where cust_id=9
) amountsDon't try to Join and group by command inside those 2 subquerys. Try it outside. That could arise some thing horrible Things.
-
Try this:
select sum(credit.amount) credit_amount,0 payment_amount from credit where cust_id=9
UNION
select 0 credit_amount, sum(payment.amount) payment_amount from payment where cust_id=9Try Another one:
Select * from amounts
(
select sum(credit.amount) credit_amount,0 payment_amount from credit where cust_id=9
UNION
select 0 credit_amount, sum(payment.amount) payment_amount from payment where cust_id=9
) amountsDon't try to Join and group by command inside those 2 subquerys. Try it outside. That could arise some thing horrible Things.
-
U can use NULL or '' insted of 0. it is to create an column which will let u to UNION 2 queries. otherwise u'll face some thing like this: With Extra Column( 0 was responsible for this) Client_amout, Pay_Amout 455,0 0,3454 0,565 565,0 No Extra Column( 0 wasn't) amout 455 3454 565 565 What is client_amount or what is pay_amount? Can u determine? I'm giving an examp1e query that i used in my IMS project:
--with max_price, only for browsing items duplicating
Create or Replace View V_ITEM_STOCK
AS
SELECT DERIVEDTBL_2.ITEMID, ITEM_2.ITEMNAME, ITEM_2.MODEL, ITEM_2.DESCRIPTION, ITEM_2.STORETYPE, ITEM_2.UNITPRICE,
DERIVEDTBL_3.MAX_PRICE, DERIVEDTBL_2.TOTAL_P, DERIVEDTBL_2.TOTAL_S,
DERIVEDTBL_2.TOTAL_P - DERIVEDTBL_2.TOTAL_S AS REMAINING
FROM (SELECT ITEMID, SUM(TOTAL_P) AS TOTAL_P, SUM(TOTAL_S) AS TOTAL_S
FROM (SELECT ITEM.ID AS ITEMID, DECODE(SUM(PURCHASEDETAILS.QUANTITY), NULL, 0, SUM(PURCHASEDETAILS.QUANTITY)) AS TOTAL_P,
0 AS TOTAL_S
FROM ITEM LEFT OUTER JOIN
PURCHASEDETAILS ON ITEM.ID = PURCHASEDETAILS.ITEMID
WHERE (ITEM.ID <> '-999')
GROUP BY ITEM.ID
UNION
SELECT ITEM_1.ID AS ITEMID, 0 AS TOTAL_P, DECODE(SUM(SALESDETAILS.QUANTITY), NULL, 0, SUM(SALESDETAILS.QUANTITY))
AS TOTAL_S
FROM ITEM ITEM_1 LEFT OUTER JOIN
SALESDETAILS ON ITEM_1.ID = SALESDETAILS.ITEMID
WHERE (ITEM_1.ID <> '-999')
GROUP BY ITEM_1.ID) DERIVEDTBL_1
GROUP BY ITEMID) DERIVEDTBL_2 INNER JOIN
ITEM ITEM_2 ON DERIVEDTBL_2.ITEMID = ITEM_2.ID INNER JOIN
(SELECT ITEMID, MAX(MAX_PRICE) AS MAX_PRICE
FROM (SELECT ITEMID, MAX(RATE) AS MAX_PRICE
FROM SAL -
U can use NULL or '' insted of 0. it is to create an column which will let u to UNION 2 queries. otherwise u'll face some thing like this: With Extra Column( 0 was responsible for this) Client_amout, Pay_Amout 455,0 0,3454 0,565 565,0 No Extra Column( 0 wasn't) amout 455 3454 565 565 What is client_amount or what is pay_amount? Can u determine? I'm giving an examp1e query that i used in my IMS project:
--with max_price, only for browsing items duplicating
Create or Replace View V_ITEM_STOCK
AS
SELECT DERIVEDTBL_2.ITEMID, ITEM_2.ITEMNAME, ITEM_2.MODEL, ITEM_2.DESCRIPTION, ITEM_2.STORETYPE, ITEM_2.UNITPRICE,
DERIVEDTBL_3.MAX_PRICE, DERIVEDTBL_2.TOTAL_P, DERIVEDTBL_2.TOTAL_S,
DERIVEDTBL_2.TOTAL_P - DERIVEDTBL_2.TOTAL_S AS REMAINING
FROM (SELECT ITEMID, SUM(TOTAL_P) AS TOTAL_P, SUM(TOTAL_S) AS TOTAL_S
FROM (SELECT ITEM.ID AS ITEMID, DECODE(SUM(PURCHASEDETAILS.QUANTITY), NULL, 0, SUM(PURCHASEDETAILS.QUANTITY)) AS TOTAL_P,
0 AS TOTAL_S
FROM ITEM LEFT OUTER JOIN
PURCHASEDETAILS ON ITEM.ID = PURCHASEDETAILS.ITEMID
WHERE (ITEM.ID <> '-999')
GROUP BY ITEM.ID
UNION
SELECT ITEM_1.ID AS ITEMID, 0 AS TOTAL_P, DECODE(SUM(SALESDETAILS.QUANTITY), NULL, 0, SUM(SALESDETAILS.QUANTITY))
AS TOTAL_S
FROM ITEM ITEM_1 LEFT OUTER JOIN
SALESDETAILS ON ITEM_1.ID = SALESDETAILS.ITEMID
WHERE (ITEM_1.ID <> '-999')
GROUP BY ITEM_1.ID) DERIVEDTBL_1
GROUP BY ITEMID) DERIVEDTBL_2 INNER JOIN
ITEM ITEM_2 ON DERIVEDTBL_2.ITEMID = ITEM_2.ID INNER JOIN
(SELECT ITEMID, MAX(MAX_PRICE) AS MAX_PRICE
FROM (SELECT ITEMID, MAX(RATE) AS MAX_PRICE
FROM SAL