Subquery possible in a straight table?

[#1]

Hi.

I am using a straight table with the intention of producing a single total…

I need to count the number of hospital beds in use per day – it must be a distinct count as one bed may have more than one patient over the course of 24 hours.

I then need to sum the daily figures to produce a monthly figure.

In SQL it looks like this:

select SUM(Beds) as TotalBeds

from (select full_date, count(distinct BedID) as Beds

        from #temp

        where BedOpen = 1

        group by full_date) sub

I came up with this but the figure is miles out: =sum(distinct total <INP_full_date> if(INP_BedOpen=1,INP_BedID))

I can get the correct figure if I sum the number of rows in a straight table – the problem is I just to display a single figure rather than numerous rows.

Any suggestions?

Thanks, Michael.

[swuehl · Accepted solution]

It might be helpful if you could post your current dimension(s) and expressions in the table.

From your SQL, I would suggest

=sum( aggr( count({<BedOpen={1}>} distinct BedID), full_date))

[#3]

Many thanks swuehl.

I tried your code – it’s returning a value of 0.

In my table I have the dimension full_date

And the expression (for which I have set Total Mode to Sum of Rows):

=count(distinct if (BedOpen = 1, BedID))

The sum total is giving the correct total.

What I need to do is reproduce the sum total independently from the table (for example in a text box).

Thanks again, Michael.

[swuehl]

Michael,

I think the expression should work in principal, see also attached sample (the textbox is using my expression).

Have you checked the exact spelling / cases of all field names etc?

Could you check my attached sample and compare it to your data model? Maybe I have made assumptions that are not correct.

Regards,

Stefan

[#5]

Perfect – thanks Stefan, really appreciate it.