Query help. Use the Sum function here?...

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

SQL Server 2000 Forums

SQL Server Development (2000)

Query help. Use the Sum function here?...

Author

Topic

cbrinson
Starting Member

36 Posts

Posted - 2002-03-27 : 16:40:47

I have a message board app and I want to generate stats that show me how many messages are posted daily over a period of time. Basically, I want trend data to show me if the message board is stagnating, getting more popular etc.

The following two queries almost give me what I want but I would like to be able to combine the two result sets somehow:

SELECT COUNT(*) As total
,Year(wr.thread_start_date) As Year
,Month(wr.thread_start_date) As Month
,Day(wr.thread_start_date) As Day
FROM webboardII_root wr
GROUP BY Year(wr.thread_start_date),Month(wr.thread_start_date),Day(wr.thread_start_date)
ORDER BY Year(wr.thread_start_date),Month(wr.thread_start_date),Day(wr.thread_start_date)

SELECT COUNT(*) As total
,Year(wc.post_date) As Year
,Month(wc.post_date) As Month
,Day(wc.post_date) As Day
FROM webboardII_children wc
GROUP BY Year(wc.post_date),Month(wc.post_date),Day(wc.post_date)
ORDER BY Year(wc.post_date),Month(wc.post_date),Day(wc.post_date)

I have one table for parent threads and another for replies. However, I would like to find a way for the query to add the two result sets together rather than doing that programmatically. I appreciate any ideas that you might have.

Thanks,
Chris

Jay99

468 Posts

Posted - 2002-03-27 : 16:46:24



select
    a.total + b.total as total,
    a.[year] as [year],
    a.[month] as [month],
    a.[day] as [day]
from
(    
SELECT COUNT(*) As total 
,Year(wr.thread_start_date) As Year 
,Month(wr.thread_start_date) As Month 
,Day(wr.thread_start_date) As Day 
FROM webboardII_root wr 
GROUP BY Year(wr.thread_start_date),Month(wr.thread_start_date),Day(wr.thread_start_date) 
ORDER BY Year(wr.thread_start_date),Month(wr.thread_start_date),Day(wr.thread_start_date) )a

inner join (

SELECT COUNT(*) As total 
,Year(wc.post_date) As Year 
,Month(wc.post_date) As Month 
,Day(wc.post_date) As Day 
FROM webboardII_children wc 
GROUP BY Year(wc.post_date),Month(wc.post_date),Day(wc.post_date) 
ORDER BY Year(wc.post_date),Month(wc.post_date),Day(wc.post_date) ) b

    on a.[year] = b.[year] and a.[month] = b.[month] and a.[day] = b.[day]

it is bad practice to used reserved words as column aliases

Jay
<O>

EDIT: now an inner join may not be exactly what you want here, but you get the idea...

Edited by - Jay99 on 03/27/2002 16:47:20

cbrinson
Starting Member

36 Posts

Posted - 2002-03-27 : 17:17:27

Great reply! The only thing I had to change was that you can't have the order by within each subselect. (I don't think that is the right term) But rather at the end of the statement:

ON a.[year] = b.[year] and a.[month] = b.[month] and a.[day] = b.[day]
ORDER BY a.[year], a.[month], a.[day]

I wish I knew more about this kind of query. Unfortunately I am self taught and don't know much about the more complex queries. If you have a minute could you explain exactly what you are doing in that query? I know you said the INNER JOIN might not be the best join here. Do you think the LEFT OUTER JOIN would be the best choice? If for some reason the original parent thread (the left table) had been deleted then I don't think you would really want to return the total number of child threads for that parent as part of that day's total. The left outer join would accomplish this, correct?

Thanks again for the help!

Chris

Edited by - cbrinson on 03/27/2002 17:19:04

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-28 : 06:26:56

A LEFT OUTER JOIN would achieve what you are after - ie. all rows from the "root" table plus data from the child table where there is a match. No rows from child table where there is no matching "root" table (ie. if you delete it as described - should your database not delete child records if the root record is deleted to avoid orphaned child records?)

To describe the joins generically...

An INNER JOIN returns only rows that match from both tables.

A LEFT OUTER JOIN returns ALL rows from the table on the left of the join with data from the right hand table if there is a matching row (any reference to fields in the right hand table where there is not matching row in the right hand table will return NULL).

A RIGHT OUTER JOIN is just the reverse of the LEFT OUTER JOIN.

============================
Chairman of
The NULL Appreciation Society
"Keep NULLs as NULL"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-03-28 : 07:48:07

A FULL OUTER JOIN would be better here unless you can guarantee that all the replies occur on a day with (any) parent message. The join is on date, not thread, so whether or not things are deleted is largely irrelevant.
The trouble with using a FULL OUTER JOIN here is that you will have to change all the outer references such as a.[year] into COALESCE(a.[year], b.[year]).
It's certainly a lot simpler from the syntax point of view to use a UNION instead:


SELECT COUNT(*) AS Total,
  YEAR(dt) AS "Year",
  MONTH(dt) AS "Month",
  DAY(dt) AS "Day"
FROM (
    SELECT thread_start_date AS dt FROM webboardII_root
    UNION ALL SELECT post_date FROM webboardII_children
  ) AS message_dates
GROUP BY YEAR(dt), MONTH(dt), DAY(dt)
ORDER BY YEAR(dt), MONTH(dt), DAY(dt)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources