SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Newer than a Newbie....
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

RonvdSchaaf
Starting Member

Netherlands
7 Posts

Posted - 10/05/2012 :  06:32:17  Show Profile  Reply with Quote
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
2916 Posts

Posted - 10/05/2012 :  09:05:00  Show Profile  Reply with Quote
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.

Go to Top of Page

RonvdSchaaf
Starting Member

Netherlands
7 Posts

Posted - 10/05/2012 :  09:49:19  Show Profile  Reply with Quote
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
Go to Top of Page

RonvdSchaaf
Starting Member

Netherlands
7 Posts

Posted - 10/05/2012 :  09:55:16  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/06/2012 :  00:13:28  Show Profile  Reply with Quote
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/

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17441 Posts

Posted - 10/06/2012 :  00:28:40  Show Profile  Reply with Quote
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
Go to Top of Page

RonvdSchaaf
Starting Member

Netherlands
7 Posts

Posted - 10/06/2012 :  05:09:02  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17441 Posts

Posted - 10/06/2012 :  07:25:14  Show Profile  Reply with Quote
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

Go to Top of Page

RonvdSchaaf
Starting Member

Netherlands
7 Posts

Posted - 10/06/2012 :  10:44:57  Show Profile  Reply with Quote
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...
Go to Top of Page

RonvdSchaaf
Starting Member

Netherlands
7 Posts

Posted - 10/06/2012 :  10:46:28  Show Profile  Reply with Quote
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..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/06/2012 :  11:46:42  Show Profile  Reply with Quote
use a union all between tables and then apply count over required fields.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

RonvdSchaaf
Starting Member

Netherlands
7 Posts

Posted - 10/06/2012 :  17:40:01  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/07/2012 :  12:56:45  Show Profile  Reply with Quote
what i suggested is in lines of what Andrew Murphy suggested

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000