CODEWITHSUNDEEP
mysqlsql
Samuel
Samuel

Reputation: 220

Doing calculation on SQL count() value

I wish to calulate the item in the warehouse and calculate the free space in the warehouse, then output to a table with column of Inshelf and free and I come up with something like these.

SELECT COUNT(*) as Inshelf, free=10-Inshelf
  FROM stock.stockproduct 
 WHERE StockNum = 1
   AND checkOutData is NULL;

Appearly it won't work, are there having any simple solution for this kind of problem?

Upvotes: 2

Views: 277

Answers (2)

Samuel
Samuel

Reputation: 220

Work !!

SELECT InShelf, 10 - InShelf
FROM (
    SELECT count(*) AS Inshelf, count(*) AS free
    FROM stock.stockproduct
    WHERE StockNum = 1 AND checkOutData IS NULL

) AS a;

Thank all, and sry for being stupid =.='

Upvotes: 0

Marc B
Marc B

Reputation: 360602

This can't work - results of aggregate functions (e.g. count()) are not available until after all of the records have been considered. You need to this with a subquery, doing the count in the subquery, the your subtraction in the outer one. Without knowing your DB schema, this is at best a guess, but...

SELECT InShelf, free - InShelf
FROM (
    SELECT count(*) AS Inshelf, free
    FROM stockproduct
    WHERE StockNum = 1 AND checkOutData IS NULL
    GROUP BY somefield
) AS a

Upvotes: 3

Related Questions