Save row counts of multiple query results

Question

Post reply

Save row counts of multiple query results

solig.fi

SSC Journeyman

Points: 80
More actions
August 3, 2010 at 12:55 am

#227372

Hi,
I have quite many queries and i would like to save its row count results into a single table, also by adding some kind of identifier to the row count results.
this is what i have for now, which works not how i would like to output the results;
select count(*) as 'FI' from
(
select
land1
from kna1
where land1 = 'FI'
) count_FI
UNION
select count(*) as 'SE' from
(
select
land1
from kna1
where land1 = 'SE'
) count_SE
The result from the above query is :
FI (Column name)
100 (this is result of the 2nd query, but its difficult to know, as it takes into account the lowest no. of rows)
123 (result of the 1st query, for FI)
i would like to have it outputed in a certain format if its even possible.
Result_Description Row_Count Result_Date
SE_Country 100 03/08/2010
FI_Country 123 03/08/2010
also these results to be saved in another table, along with a date, i guess that could just be automated when the record was created?
Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 1

I think what you're looking for is:

select 'SE_Country' as col1, count(*) from .....

union all

select 'FE_Country' as col1, count(*) from ....

union all

solig.fi SSC Journeyman Points: 80 More actions · Answer 2

thanks a lot! that works great like i want it to do! 😀

LutzM SSC Guru Points: 107049 More actions · Answer 3

Wouldn't it be easier to do it in one query? Or am I missing the obvious?

SELECT land1, COUNT(*)

FROM kna1

WHERE land1 IN('SE', 'FI')

GROUP BY land1

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]