help with a query (totals) - 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

SQL Server 2000 Forums

SQL Server Development (2000)

help with a query (totals)

Author

Topic

petem
Starting Member

44 Posts

Posted - 2004-07-22 : 04:52:17

Hi guys,

Ive been using SQL for a few years just for really basic queries for asp websites. As such Ive never really used in depth queries - just simple SELECT, UPDATE, DELETE etc.

I need to return some info regarding stats.
The table includes the following columns (as well as others):

SessionID, MarketingSourceID, SectionID

I would like to show the total number of hits on section 1 (homepage) for each MarketingSourceID

ie:
Src  |  hits
1        46
2        135
3        85
4        201

any help much appreicated,

Pete

cas_o
Posting Yak Master

154 Posts

Posted - 2004-07-22 : 04:58:10

SELECT MarketingSourceID as Src, count(SectionID) hits
FROM table
WHERE SectionID = 1
GROUP BY MarketingSourceID

;-]... Quack Waddle

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-22 : 04:58:42

Assuming 1 row in the table for each hit on a section from a particular marketingsource...


SELECT MarketingSourceID, COUNT(1) AS Hits
FROM MyTable
WHERE SectionID=1
GROUP BY MarketingSourceID
ORDER BY MarketingSourceID

petem
Starting Member

44 Posts

Posted - 2004-07-22 : 05:01:28

Thanks!

Pete

petem
Starting Member

44 Posts

Posted - 2004-07-22 : 05:34:16

Thanks for the help so far, that query works great - and it's simpler than I thought. (Isn't hindsight great!)

Just a little addition :)

I would like to include the names of the sources which are held in another table (sourcename, sourceid)

so far I have:

SELECT Sources.Source as srcName, WebStats.MarketingSourceID as SrcID, count(WebStats.SectionID) hits
FROM WebStats
INNER JOIN Sources
ON webstats.MarketingSourceID = Sources.SourceID
WHERE SectionID = 1
GROUP BY MarketingSourceID, Source
ORDER BY Source

This works fine but, if a source has zero hits then it is not returned. I want it to return all the sources in the source table and display a zero if that is the case. I know the cause of this - source.source must have a match in the webstats table to return in the query - but I dont know how to remedy it...

thanks

Pete

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-22 : 06:13:11

Use a Left Join, thus..


SELECT Sources.Source as srcName, ISNULL(WebStats.MarketingSourceID as SrcID, 'None'), count(1) hits
FROM Sources
LEFT JOIN WebStats
ON Sources.SourceID=webstats.MarketingSourceID
WHERE SectionID = 1 
GROUP BY MarketingSourceID, Source
ORDER BY Source

petem
Starting Member

44 Posts

Posted - 2004-07-22 : 07:22:24

Thanks for that Jason,

I am using the query below:


SELECT Sources.Source as srcName, ISNULL(WebStats.MarketingSourceID, 0) as SrcID, count(1) hits
FROM Sources
LEFT JOIN WebStats
ON Sources.SourceID=WebStats.MarketingSourceID
WHERE (WebStats.SectionID = 1) AND (Sources.Active = 1) AND (Sources.Deleted = 0)
GROUP BY MarketingSourceID, Source
ORDER BY Source

However, this still doesnt bring back one source which does not have any hits in the WebStats table - ie: the ID '1' is not in the WebStats table.

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-22 : 07:30:15

Hang on - I think I know the problem. You need to grab your counts first, so put them in a derived table (WS in the example below). Then the LEFT JOIN should return all Sources in the Sources table, with MarketingSourceID and Hits set to NULL if there are no records for that source in the Webstats table.


SELECT Sources.Source as srcName, WS.MarketingSourceID, WS.Hits
FROM Sources
LEFT JOIN SELECT (MarketingSourceID as SrcID, count(1) hits
                   FROM WebStats
                   WHERE SectionID=1
                   GROUP BY MarketingSourceID) AS WS
          ON Sources.SourceID=WS.MarketingSourceID
WHERE Sources.Active = 1 AND Sources.Deleted = 0
ORDER BY Source

petem
Starting Member

44 Posts

Posted - 2004-07-22 : 08:02:26

hey thanks - that's fantastic,

Pete

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources