I have this advanced query:


SELECT {Products}.*

FROM {Products}


But the Products table has a boolean column "IsInStock", and I want to count the number of products that have IsInStock at true and the number of products that have InStock at false so that the result of the query has this information. Do you know properly achieve this using SQL?




Hi,

Why are you using SQL and not an aggregate? In An aggregate you can have product as source and than you create 2 new attributes. In one of than you use this If(product.isInStock = true, 1, 0) other instead of count true you count false. After that you just need to sum each column to have both values that you want.

Regards,

Marcelo

Marcelo Ferreira wrote:

Hi,

Why are you using SQL and not an aggregate? In An aggregate you can have product as source and than you create 2 new attributes. In one of than you use this If(product.isInStock = true, 1, 0) other instead of count true you count false. After that you just need to sum each column to have both values that you want.

Regards,

Marcelo


Thanks, but I'm using a version that doesn't have aggregates. Is not possible to count and return the number of trues and falses in the advanced query?

Solution

Hi Oscar,

in sql, what you want is done by grouping several rows of the table together that have similarities.   This is done with the group by clause.  The output of the query is in such cases restricted to information at the group level, instead of information at individual row levels.


See the sql widget you need in OS to do this : 

For Comparison, if you had the capability to use Aggregates, this is what that would look like in OS, only blue columns are part of Aggregate output:


Hope this helps you,

Dorine


Solution