Newer than a Newbie.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Newer than a Newbie....

Author

Topic

RonvdSchaaf
Starting Member

7 Posts

Posted - 2012-10-05 : 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
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2012-10-05 : 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

7 Posts

Posted - 2012-10-05 : 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

7 Posts

Posted - 2012-10-05 : 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

52326 Posts

Posted - 2012-10-06 : 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)

17689 Posts

Posted - 2012-10-06 : 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
[spoiler]Time is always against us[/spoiler]

RonvdSchaaf
Starting Member

7 Posts

Posted - 2012-10-06 : 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)

17689 Posts

Posted - 2012-10-06 : 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
[spoiler]Time is always against us[/spoiler]

RonvdSchaaf
Starting Member

7 Posts

Posted - 2012-10-06 : 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

7 Posts

Posted - 2012-10-06 : 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

52326 Posts

Posted - 2012-10-06 : 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

7 Posts

Posted - 2012-10-06 : 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

52326 Posts

Posted - 2012-10-07 : 12:56:45

what i suggested is in lines of what Andrew Murphy suggested

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources