0

select companyname,sum(sale) as TotalSale,sum(purchase) as TotalPurchase from invoice group by companyname Having companyname IN (select companyname from invoice)

It returns sum only for those column whose all sum are not blank, how can i show blank sum values

I mean there are 7 companies but only 4 records are shown. I want to display all seven.

4
Contributors
16
Replies
17
Views
6 Years
Discussion Span
Last Post by drmrkrch
Featured Replies
  • Another way (older way) to join two oracle tables together with outer join syntax would be to do it the following way. [icode] select c.companyname, sum(nvl(i.sales,0)) from customers c, invoices i where c.companyname = i.companyname (+) group by c.companyname [/icode] The (+) syntax goes where you expect the nulls to … Read More

0
select companyname,sum(sale) as TotalSale,sum(purchase) as TotalPurchase from invoice group by companyname

I used the above query to get the total sale and purchase belong to following company

A , B ,C ,D ,E ,F ,G

only A,C,E,G have sale and purchase value other have no sale or purchase record.

so I got A 12 13 ,C 12 15 , E 15 89 , G 14 89 but i want rest company to show 0 if there sum of sale or purchase are not present.

0

Following are the record
Company purchase sale
A 1 1
A 1 2
B 5 7

now the same above query will only show A and B company sale and purchase rest of companies values will be ignored and this is what i dont want.

0

You will need to do an outer join select all from one table while getting the matches from another. Why are you joining on company name instead of an I'd value? Mark

0

Use the nvl() function around the sums and remove the having portion of your query. I didn't realize that you were calling the same table twice which is is unnecessary for your query. The nvl function will prevent summing nulls which isn't possible. It will convert nulls trouble zero.

0
select companyname,sum(sale) as TotalSale,sum(purchase) as TotalPurchase from invoice group by companyname

I have already modified by query....

Also Nvl dont work.....

Edited by Majestics: n/a

0

100% Oracle Database...... :) I m making report which take all company name there purchase and sale SUMS and difference, some company can have no sale and purchase so atleast there name must be listed with 0 on both sale and purchase... Thats what all i want.

0

I agree with web-guy's earlier suggestion that you probably need to outer join your customer table to your invoice table for the sums. I assume that the customers who have no orders have no records in the invoice table. If they aren't in the table, they aren't going to appear in your group by results.

Try using [customer table] left outer join [invoice table] on the customer Ids.

Edited by Ezzaral: n/a

2

Another way (older way) to join two oracle tables together with outer join syntax would be to do it the following way.

select c.companyname, sum(nvl(i.sales,0))
from customers c, invoices i 
where c.companyname = i.companyname (+)
group by c.companyname

The (+) syntax goes where you expect the nulls to occur

Votes + Comments
agree
I DONT HAVE WORDS.....
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.