Doing calculation on SQL count() value

Question

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?

Can you include some data for testing, table structure and specify on wich RDBMS are you working, MS SQL Server, MySQL, Oracle. — Yaroslav
– Yaroslav, Commented Sep 22, 2012 at 16:24
and the schema is every simple. should be two column with Inshelf and free. and one row of result — Samuel
– Samuel, Commented Sep 22, 2012 at 16:26
WHERE StockNum = 1 AND checkOutData is NULL - you have no checkOutData or StockNum column in the 'schema' you just provided. — mrmryb
– mrmryb, Commented Sep 22, 2012 at 16:34

Marc B · Accepted Answer · 2012-09-22 16:33:16Z

3

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

answered Sep 22, 2012 at 16:33

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

2 Comments

Samuel Over a year ago

Unknow columne 'free' in field list

Ken White Over a year ago

@user1660416: Look at what you wrote in your question and what Marc wrote in his answer, and then use your head to figure out what free is in the field list and fix it. You really should learn to try to actually read and understand things, not just copy and paste them. Marc, +1. Nice answer.

Samuel · Accepted Answer · 2012-09-22 17:14:21Z

0

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

answered Sep 22, 2012 at 17:14

Samuel

2202 silver badges14 bronze badges

Collectives™ on Stack Overflow

Doing calculation on SQL count() value

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related