nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

multiple select count (*) in one query

valence

hi,
i have three query :
- select count (*) as total from t1
- select count (*) as lower from t1 where t1.var<value
- select count (*) as upper from t1 where t1.var>value

i want to view in the same report lower and upper parameter and their percentage ( lower * 100 / total and upper * 100 / total) from the total. i need your help, i have a big project with birt and i can't create the first report, i'm new in this domain. thanks. if i'm not clear tel me. good night.

Find more posts tagged with

Comments

mwilliams

Hi valence,

What do you have set up in your report so far? Do you have all 3 dataSets made?

Michel

You could use:

select
(select count(*) from t1) as total,
(select count(*) from t1 where t1.var < value) as lower,
(select count(*) from t1 where t1.var > value) as upper
from
dual

if your database knows the pseudo table "dual", like MySQL or Oracle

valence

the pseudo table "dual" does not exist in Postgresql Server !!
did you know other solution ??
it must exist an alternative to use multiple data set in the same expression builder.

SBCCS

<blockquote class='ipsBlockquote' data-author="valence">the pseudo table "dual" does not exist in Postgresql Server !! 
did you know other solution ?? 
it must exist an alternative to use multiple data set in the same expression builder.</blockquote>
 
If you have a common number of row you could try a Union.

Michel

How about:

select
sum(1) as total,
sum(case when var < value then 1 else 0 end) as lower,
sum(case when var > value then 1 else 0 end) as upper
from
t1;

waelos

thank you, that is exactly what i need !!