Using as operator

Hello list members.

I was trying to use the library and here is one thing, I didn't figure how
to use it. Will be thankful, if you could help. Here is my example (not
working):

(defun number-of-goods-you-have-the-most? ()
  (clsql:select [item_id] [as [count [*]] "num"]
        :from [table-name]
        :where
        [is [null [sale_date]]]
        :group-by [item_id]
        :order-by '(("num" :desc))
        :limit 1))

the non-working parts are [as [count [*]] "num"] and, possibly, :order-by
'(("num" :desc)). What I was trying to do, should be quite obvious from the
function name, yet, I'll explain just to make sure.

The table contains field item_id (which is not unique), it also has a field
sale_date, which stores the date the item was sold. So, if the item was not
sold (no sale_date) I would like to count the number of that item kind and
return the number of items of the same kind, which are found the most in
the table. Consider the example below:

item_id | sale_date
---------------------------
1 | NULL
1 | 2000-01-10
2 | NULL
3 | 2000-01-01
3 | NULL
1 | NULL
3 | 2000-01-01

The function then would return 2 because there are 2 items of item_id '1',
which have not been sold yet.

Thanks in advance!

_______________________________________________
CLSQL mailing list
[email protected]
http://lists.b9.com/cgi-bin/mailman/listinfo/clsql