Selecting unique records (advanced)

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 2005 Forums

Transact-SQL (2005)

Selecting unique records (advanced)

Author

Topic

hephie
Starting Member

15 Posts

Posted - 2008-12-20 : 17:45:32

I have a table with 3 columns. Everytime a customer plays a track a new record is inserted in the table.

An example table below:

I need a select statement that gives all unique tracks (TrackID) played per month, and each month represented in a column.
The select query should return everything in this format. For the above example it would be this:

Anyone?
Many thanks in advance!!

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-12-21 : 01:57:34

I dont have db handy to test, buy pseudo code is:


select customerid, trackid, sum(case when datepart(month, dateplayed)=1 then 1 else 0 end) [Jan]
from YourTable
group by customerid, trackid
order by 1,2

You can follow this example to do the other months.

Nathan Skerl

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-12-21 : 01:58:53

also, be aware that data from multiple years will be summed together, Jan 2006 + Jan 2007, etc.

Nathan Skerl

hephie
Starting Member

15 Posts

Posted - 2008-12-21 : 07:06:57

sorry, i made a mistake in the first post.
I only need the customerid and the amount of UNIUQE tracks played.

so the result of the above example would be:

Customer----|November---|December
1-----------|2----------|2

please review.
thanks!

I understand that data of different years will be combined. That is not a problem :)

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-12-21 : 14:46:35

Just remove TrackId from the query

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-21 : 15:48:52

Looks like your dateplayed col is char or varchar.

Select customerid,
Sum(case when substring(dateplayed,charindex('/',dateplayed)+1,2) in ('01','1/') then 1 else 0 end) as January
...................................................................
Sum(case when...................................................................................= '12' then 1 else 0 end) as December
From table
Group by customerid
Order by customerid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources