Subtract values from two tables and returns values for all fields and if no values in table 2 return the value of the first table

Question

0.00/5 (No votes)

See more:

SQL

select SUM(item_store.quantity) -  (select SUM(transaction_items_store.quantity) AS exp2  from  transaction_items_store 
                              where  transaction_items_store.item_no=item_store.item_no) as Q
                              FROM item_store 
                              group by   item_store.item_no

===========the output is =======

VB

4
10
7
NULL
NULL

Posted 18-Oct-13 7:08am

jumah

Add a Solution

Comments

Kschuler 18-Oct-13 13:49pm

What is your question? Can you explain more? Tell us what the code is currently doing and what you WANT it to do. Tell us if you are getting an error message and what the exact error message is.

jumah 18-Oct-13 14:09pm

there is no error with the code...i have 2 tables(item_store and transaction_items_store) evrey one hase a quantity column i want to subtract the 2 coulmns but if the Column of table 2 hase no value it return null value as shown in my output
example
item_store transaction_items_store q
20 16 4
11 10 10
12 5 7
8 null null
7 null null

i want the result to be
4
10
7
8
7

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kschuler · Accepted Answer · 2013-10-18T08:41:00

Solution 1

It sounds like your real question is "How can I select a null value as a zero[^]?"

The actual syntax will depend on what actual database you are using. This google result[^] has a couple of options for some different databases.

Hope this helps.

Posted 18-Oct-13 8:41am

Kschuler

Comments

jumah 18-Oct-13 14:53pm

Many thanks it is solved.....

jumah · Accepted Answer · 2013-10-18T08:54:00

SQL

select SUM(item_store.quantity) -
  (select IFNULL(SUM(transaction_items_store.quantity),0) AS exp2  from  transaction_items_store
                              where  transaction_items_store.item_no=item_store.item_no) as Q
                              FROM item_store
                              group by   item_store.item_no

Subtract values ​​from two tables and returns values ​​for all fields and if no values in table 2 return the value of the first table