SQL Subquery help!

[SQL]

I have a report ouput requirement like this..

Age Group Sex Year counts1 Year counts 2 Year counts 3
62 Senior M 15 20
29
69 Senior F 21 23
25

My year counts1, Year Counts 2, Year counts 3 results are coming from
three different tables (a,b,c)

Select age, group, year counts1 = select count(*) from a, year
counts2=select count(*) from b, year counts3=select count(*) from c.
From..............................................................

My real life counts for a,b,c are huge in millions and involves joins
with several other tables. Is there a better way to write an efficient
SQL to get these results.

[Michael Coles]

Without knowing what you're actually doing it's hard to say whether there's
a more efficient way to do it or not. One thing you might consider is
running these queries during down-time (schedule a job to run them at night
for instance) and store the results in a pre-aggregated report table. Then
you just query that one table in the morning to get your report results.
This works if the data doesn't have to be up-to-the-minute; that is, the
data at close of business yesterday is recent enough for the reports you're
trying to generate today.

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"SQL" <texas...@gmail.com> wrote in message
news:de896812-b3ab-41b5...@j24g2000yqa.googlegroups.com...

[--CELKO--]

Your narrative is too vague to do anything but guess. Are these
tables unrelated? What are the other tables to which they join like?
Do they overlap? Etc.

This is why we ask for DDL and clear specs.

But here is a guess at improvement. Scalar subquery expressions in a
SELECT list are very expensive, so don't use them.

SELECT *
FROM (SELECT person_age, age_group, sex_code, COUNT(*), 'Table_A'
FROM Table_A
GROUP BY person_age, age_group, sex_code
UNION ALL
SELECT person_age, age_group, sex_code, COUNT(*), 'Table_B'
FROM Table_B
GROUP BY person_age, age_group, sex_code
UNION ALL
SELECT person_age, age_group, sex_code, COUNT(*), 'Table_C'
FROM Table_A
GROUP BY person_age, age_group, sex_code)
AS Report (person_age, age_group, sex_code, group_cnt, data_source);

Trying to format your display in the DB side of the system is always a
mistake. Front end tools will do a much better job. You need to
quite using the old proprietary "<column name> = <expression>" syntax
and start using the Standard "<expression> AS <column name>"
instead.

Does the original data have the bitrthdates or the age as shown? Why
not calculate the age groups with a CASE expression and save a few
disk reads? Look up the ISO sex codes for future reference.

That is all I have without more information.

[TheSQLGuru]

I would aggregate each table's counts first then join those 3 on age, group,
sex.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

"SQL" <texas...@gmail.com> wrote in message
news:de896812-b3ab-41b5...@j24g2000yqa.googlegroups.com...