Query

[Majestics]

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.

[debasisdas]

I think you do not need the HAVING part of the SQL.

[Majestics]

Yes u are right as always.... But what about my problem....

[debasisdas]

can u post some sample data.

[Majestics]

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.

[Majestics]

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.

[Majestics]

Let me clear it i m making report

[drmrkrch]

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

[Majestics]

Please take my above query and place a outer join in correct manner....

[drmrkrch]

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.

[Majestics]

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.....

[drmrkrch]

Is this oracle DB? Or someother flavor?

[Majestics]

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.

[drmrkrch]

Nvl(sales,0) and nvl(purchase,0) is where you need to change.

[Majestics]

NVL WORKED, but my problem isnt solved yet

[Ezzaral]

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.

[drmrkrch]

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