Create sql query string

Question

i have a problem to create query for table

mouventDeStock

|| idMv || dateMv ||qteMv || typeMV(bit) || codeMv ||

the feild type is either 0 or 1 if 0 means out of the stock , if 1 means means input of stock

i wanna calculate the qte witch is equal to

qte = ( sum(qteMv) where typeMv = 1 )  - ( sum(qteMv) where typeMv = 0 )

how i can't do it in sql

(select sum(mvQte) from mouvementDeStock 
 where (mvType = 1 and mvCode = 'ART_18'))
 -
 (select sum(mvQte) from mouvementDeStock 
 where (mvType = 0 and mvCode = 'ART_18')

but it's not working at all

unlimit · Accepted Answer · 2013-10-13 07:40:43Z

1

Try this...

Select Y.S - X.S As Sum from
(select sum(qteMv) S from mouventDeStock where typemv=1 and codemv = 'ART_18') Y,
(select sum(qteMv) S from mouventDeStock where typemv=0 and codemv = 'ART_18') X;

answered Oct 13, 2013 at 7:40

unlimit

3,7622 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

advseo32 Over a year ago

yes it works but i try to assign the query isn't works i want to do this table.feild = result of query ;

unlimit Over a year ago

Are you updating a existing record or inserting into a new table? Post the table definition too.

advseo32 Over a year ago

i want to update a only one record , my table is about sales products and buying products and i have separte them with type of mouvment mvType, and i want to calculate total(QtySalesProducts) - total(QtyBuyingProducts) and Update qty in table products , i hope is clear now

advseo32 Over a year ago

how i can set sum in case of NUll as zero , because X-NULL = NULL and this is a problem

unlimit Over a year ago

Use IFNULL. Select IFNULL(Y.S, 0) - IFNULL(X.S, 0) ... See sqlite.org/lang_corefunc.html#ifnull

CL. · Accepted Answer · 2013-10-13 08:39:15Z

0

You can use subqueries to calculate the subtotals, but your entire statement must be a valid SQL statement, so you need to wrap another SELECT around them:

SELECT (SELECT SUM(mvQte) FROM mouvementDeStock 
        WHERE mvType = 1 AND mvCode = 'ART_18')
       -
       (SELECT SUM(mvQte) FROM mouvementDeStock
        WHERE mvType = 0 AND mvCode = 'ART_18')

If you want to be clever, you can do this with a single sum, by making all out-of-stock mvQte values negative by multiplying them with -1:

SELECT SUM(mvQte * CASE mvType WHEN 0 THEN -1 ELSE 1 END)
FROM mouvementDeStock
WHERE mvCode = 'ART_18'

(CASE expression documentation)

answered Oct 13, 2013 at 8:39

CL.

182k18 gold badges241 silver badges282 bronze badges

Collectives™ on Stack Overflow

Create sql query string

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related