SQL Question
Tomasz Baron asked how to combine two count(*) queries on TabA (ColA=X and ColA=Y) into one. gignez P. suggested IN (X, Y), optionally with GROUP BY, but Tom needed a single row with two columns. Rudy Fernandes gave two scalar subqueries selecting from systables. Tom then described a larger five-column grouped report and asked if a stored procedure was the only way. No answer to that.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
How to combine into a single question something like this:
select count(*) from TabA where ColA=X;
select count(*) from TabA where ColA=Y;
Thanks for all answers
If you want the grand total, just use
select count(*) form TabA where ColA in(X, Y);
if you want each subtotal
select count(*) form TabA where ColA in(X, Y)
group by ColA;
this last one returns 2 rows.
Tomasz Baron <tomasz_baron@banpol.pl> a 'crit dans le message :
Hm2%4.3380$qi.88060@news.tpnet.pl...
> How to combine into a single question something like this:
>
> select count(*) from TabA where ColA=X;
> select count(*) from TabA where ColA=Y;>
>
>
> Thanks for all answers
>
>
>
Statement have to return 1 row!! (with two columns)
"gignez P." <gignez.pascal@siemens.be> wrote in message
news:8hicui$dn5$1@news.mch.sbs.de...
> If you want the grand total, just use
>
> select count(*) form TabA where ColA in(X, Y);>
> if you want each subtotal
>
> select count(*) form TabA where ColA in(X, Y)
> group by ColA;>
> this last one returns 2 rows.
>
> Tomasz Baron <tomasz_baron@banpol.pl> a 'crit dans le message :
> Hm2%4.3380$qi.88060@news.tpnet.pl...
> > How to combine into a single question something like this:
> >
> > select count(*) from TabA where ColA=X;
> > select count(*) from TabA where ColA=Y;> >
> >
> >
> > Thanks for all answers
> >
> >
> >
>
>
SELECT (SELECT COUNT(*) FROM TabA WHERE ColA = 'X'), (SELECT COUNT(*) FROM TabA WHERE ColA = 'Y') FROM systables WHERE TABNAME = 'systables'; But, why this inflexibility! Rudy Tom wrote: > Statement have to return 1 row!! (with two columns) > >
Great, it was only a small part of my puzzle. Here is some new informations:
Every record returned by the select statement has to contain 5 columns:
TabA.ColA
TabA.ColB
count(*) where TabA.ColC=X
count(*) where TabA.ColC=Y
sum(TabB.ColF) where TabA.ColD=TabB.ColD and TabA.ColA=TabB.ColA
The resuls has to be - grouped by TabA.ColA, TabA.ColB and ordered by
TabA.ColA, TabA.ColB
...and example of the output :
2000-06-06 45678 12 24 64356
2000-06-06 47689 45 29 53254
2000-06-07 41676 13 14 34344
Is only stored procedure a good solution??
Thanks for answers
"Tomasz Baron" <tomasz_baron@banpol.pl> wrote in message
news:Hm2%4.3380$qi.88060@news.tpnet.pl...
> How to combine into a single question something like this:
>
> select count(*) from TabA where ColA=X;
> select count(*) from TabA where ColA=Y;>
>
>
> Thanks for all answers
>
>
>