| Author |
 Topic  |
|
|
RonvdSchaaf
Starting Member
Netherlands
7 Posts |
 Posted - 10/05/2012 : 06:32:17
|
Hello,
As the title says I'm very new to SQL and so eager to learn...
The basics are ok by me but now the hard stuff comes to learn and I really can't get my answers fast enough. So maybe here somebody can give me a good answer...
The project is I think simple...
I have a lot of tables, 1 master and many, many detail tables.
What I want is to connect all the tables and receive the number of records from the detail tables.
So 1 city_id from the master must be joined to detail_table_a on city_id, to detail_table_b on city_id, to detail_table_c on city_id and so on maybe 25 detail_tables in total..
And then I would like to know, how many records there are in detail_table_a, how many in detail_table_b, how many in detail_table_c and so on... Even if the record count in the detail_table is zero, no records found I would like to receive a Zero.
In my early days of DBase and Clipper we had so much nice functions like detail_table_a->reccount()... But how to do this in SQL??????
Please help me  , for you it must be easy...
Thnx,
Ron |
|
|
AndrewMurphy
Flowing Fount of Yak Knowledge
Ireland
2915 Posts |
Posted - 10/05/2012 : 09:05:00
|
for pure tablebased record counts
select 'a',count(*) from detaila
union all
select 'b',count(*) from detailb
etc
for record counts based on how many records in each sub table per parent record
select
parentcol, count(a.id), count(b.id)
from
parentable
left join detaila on a.code = parentcode
left join detailb on b.code = parentcode
group by parentcol
you may need a case statement within the count() or switch the count(*) to sum(case when a.code is null then 0 else 1 end) to deal with null values...search here for examples.
|
 |
|
|
RonvdSchaaf
Starting Member
Netherlands
7 Posts |
Posted - 10/05/2012 : 09:49:19
|
Hello AndrewMurphy,
Thanks for the response, I think I can understand it.. :o) not sure yet, have to try it out...
And as last you mention... search here for examples.. And the here means? In this forum (where i couldn't find examples) or anothere website?
Regards,
Ron |
|
|
|
RonvdSchaaf
Starting Member
Netherlands
7 Posts |
Posted - 10/05/2012 : 09:55:16
|
How to implement the Case statement is not clear to me as is the switch count to sum..
That's I think advanced SQL?
Ron |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47035 Posts |
Posted - 10/06/2012 : 00:13:28
|
quote:
Originally posted by RonvdSchaaf
How to implement the Case statement is not clear to me as is the switch count to sum..
That's I think advanced SQL?
Ron
you can use case statement eaxctly like its shown
ie
Aggregatefn(case when condition then value1 else value2 end)
Aggregatefn can be SUM,MIN,MAX,AVG etc
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 10/06/2012 : 00:28:40
|
quote:
DBase and Clipper
Ah it has been quite a while since i last heard of it. 
for your question, this is one way.
select m.city_id, m.master_other_col,
a_cnt = isnull(a.cnt, 0), b_cnt = isnull(b.cnt, 0), c_cnt = isnull(c.cnt, 0)
from master_table m
left join (select city_id, cnt = count(*) from detail_a group by city_id) a on m.city_id = a.city_id
left join (select city_id, cnt = count(*) from detail_b group by city_id) b on m.city_id = b.city_id
left join (select city_id, cnt = count(*) from detail_c group by city_id) c on m.city_id = c.city_id
Is city_id the primary key of the master table ?
KH
Time is always against us
|
Edited by - khtan on 10/06/2012 00:29:29 |
|
|
|
RonvdSchaaf
Starting Member
Netherlands
7 Posts |
Posted - 10/06/2012 : 05:09:02
|
Hello khtan,
Thank you for your example..
Before I start coding it in my query I would like to ask you in all the left join statements you use cnt = count(*) etc...
Is that correct? Should I use cnt over and over or should it be a_cnt, b_cnt etc....
Just wandering, to my logic the statements would count all records in the same cnt...
And city_id is indeed the primary key of the master table which only has the city_id and city_name as its fields/columns.
All other tables are companies in that city so you have the other tables like hotels, restaurants, swimmingpools etc..
And I need this SQL query to show how many hotels, restaurants etc there are in that city...
Ron |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 10/06/2012 : 07:25:14
|
quote:
select city_id, cnt = count(*) from detail_a group by city_id
the count in the above statement will give you a record count per city_id in table detail_a
is that what you want ?
KH
Time is always against us
|
|
|
|
RonvdSchaaf
Starting Member
Netherlands
7 Posts |
Posted - 10/06/2012 : 10:44:57
|
| Yes that is what I want. And I want exactly the same for all the other detail tables... There are 26 tables related to that one master table... |
|
|
|
RonvdSchaaf
Starting Member
Netherlands
7 Posts |
Posted - 10/06/2012 : 10:46:28
|
And what also is important that i receive a zero when there are no records in the related table.
So finally I want to display 26 numbers (record counts) in one screen.. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47035 Posts |
Posted - 10/06/2012 : 11:46:42
|
use a union all between tables and then apply count over required fields.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
RonvdSchaaf
Starting Member
Netherlands
7 Posts |
Posted - 10/06/2012 : 17:40:01
|
Oh my... that is also something advanced i think.. union all..
Have to look that up.. I was just going to implement the example of khtan..
What do I do with your suggestion visakh16? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47035 Posts |
Posted - 10/07/2012 : 12:56:45
|
what i suggested is in lines of what Andrew Murphy suggested
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Newer than a Newbie....
