creating a table with individual calculated cells

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)

creating a table with individual calculated cells

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-09-29 : 08:15:57

andy writes "I am familiar with selecting data from tables, agregate values etc

I can not get my head round this though. I have data that has numerouse rows in a table. Each time a member visits the site it is logged as 1 visit for Joe in period 2. So Joe may visit 10 times in period 2 and create 10 rows. Fred may have 4 rows etc, There are 12 periods per year.

I want to create a simple output which will have the record for Joe and the number of times he has visited per period the result would be something like this

Period 1 2 3 4 5 6 7 8 9 10 11 12

Joe 12 13 4 12 34 12 34 43 23 12 34 21
Fred 14 15 2 55 34 43 21 23 1 23 54 32
Stan 1 23 43 etc

any thoughts"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-09-29 : 08:18:50

Cross-tabs to the rescue:

http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

It's possible that you don't need dynamic cross tabs since your columns are fixed, a regular CASE expression could work:

SELECT Name,
Sum(CASE WHEN Month(VisitDate)=1 THEN 1 ELSE 0 END) AS [1],
Sum(CASE WHEN Month(VisitDate)=2 THEN 1 ELSE 0 END) AS [2],
Sum(CASE WHEN Month(VisitDate)=3 THEN 1 ELSE 0 END) AS [3],
...etc.
FROM myTable GROUP BY Name

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-29 : 08:21:48

select DISTINCT a.Name, p1.VisitCounts, p2.VisitCounts, .... etc
from Visits a
INNER JOIN ( SELECT Name, SUM(VisitCounts) as VisitCounts FROM Visits WHERE Period = 1 GROUP BY Name) as p1 on a.Name = p1.Name
INNER JOIN ( SELECT Name, SUM(VisitCounts) as VisitCounts FROM Visits WHERE Period = 2 GROUP BY Name) as p2 on a.Name = p2.Name

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources