Statistic report from four tables

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

Statistic report from four tables

Author

Topic

latif_qau
Starting Member

4 Posts

Posted - 2010-06-24 : 20:11:51

Hi
I have Four tables Calender, Authors, Articles, comments.
i want to create a report showing following stats

date AuthorId TotalArticles TotalComments
1/12/2009 8 2 0
1/13/2009 4 2 3
1/14/2009 5 0 0
1/15/2009 6 2 3
1/16/2009 7 0 0

Calender Table has just date given above.
Articles has date_created, article_id , author_id,
Comments has date_created, comments_id, article_id

Thanks in advance...

Khan

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-06-25 : 01:36:14

your code my differ based on which date you want to define in your report.
if you take date from articles or date from comments it will surely show you different results.

i'll just write you a sample and you change the desired date or. change the connection between the dates (using left join on the tables or making connection with date by articles.date_created and comments.date_created). all this things will affect your output, unfortunatelly i don't know your bussines process.


select
c.Date as date
,a.Author_id as AuthorId
,count(distinct a.article_id) as TotalArticles
,count(distinct com.comments_id) as TotalComments
from Calender as c
inner join Articles as a
on c.date = a.date_created
inner join Comments as com
on com.article_id = a.article_id
group by c.Date
order by c.Date

latif_qau
Starting Member

4 Posts

Posted - 2010-06-27 : 13:34:33

Thanks slimt i am trying it.....

Khan

latif_qau
Starting Member

4 Posts

Posted - 2010-06-27 : 13:56:43

my main purpose is
on date 01/01/2010 which authors post how many articles and how many commets he recieve on that date on all of his articles.
if i have three authors with id 1 2, 3
then first few lines will look like
date AuthorId TotalArticles TotalCommentsOnAllArticles
1/01/2010 1 2 0
1/01/2010 2 0 12
1/01/2010 3 3 1

Khan

latif_qau
Starting Member

4 Posts

Posted - 2010-06-27 : 15:07:58

@slimt
Thanks alot for your help after a samll change it works.

select
c.Date as date
,a.Author_id as AuthorId
,count(distinct a.article_id) as TotalArticles
,count(distinct com.comments_id) as TotalComments
from Calender as c
inner join Articles as a
on c.date = a.date_created
left join Comments as com
on com.article_id = a.article_id
group by c.Date , a.Author_id
order by c.Date

Khan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources