Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

help with a simple query??


hi all!

i've got this query:

select IDAgn as ID,IDBus as 'BusID',PubYear  as 'Year' from Chocolate
where PubYear > 2005

output is:
ID              BusID       Year
20      31693   2007
20      31693   2007
86      31759   2007
86      31759   2007
86      19348   2007
86      19348   2007
21      31810   2007
21      31810   2007
20      30825   2007
21      23770   2007
20      29019   2007
20      29019   2007
20      31839   2007
20      31839   2007
21      31030   2007

i also have other years on the pubyear column aside from 2007.
how can i make it look like :

ID           Year-07Count             Year-04Count         Year-05Count
20               10                                 1                        
  3
21               20                                 0                        
  0
86               15                                 11                      
 10

i hope i made my problem clear for you. please let me know if u need more
explanation

Start with reading up on COUNT() and GROUP BY

"ChiWhiteSox" <ChiWhite@discussions.microsoft.com> wrote in message

news:066DC731-B699-4FDF-9D9F-EC8150ADDDED@microsoft.com...

On 3?15?, ??7?53?, "Nigel A" <n@discussion.microsoft.com> wrote:

and CASE statement :)

-----------------------------------------------Reply-----------------------------------------------
On Mar 15, 3:37 am, ChiWhiteSox

declare @tbla table (id int,busid int,yearid int)

insert into @tbla values (20 ,     31693,   2007 )
insert into @tbla values (20 ,     31693,   2007 )
insert into @tbla values (86 ,     31759,   2007 )
insert into @tbla values (86 ,     31759,   2007 )
insert into @tbla values (86 ,     19348,   2007 )
insert into @tbla values (86 ,     19348,   2007 )
insert into @tbla values (21 ,     31810,   2007 )
insert into @tbla values (21 ,     31810,   2007 )
insert into @tbla values (20 ,     30825,   2007 )
insert into @tbla values (21 ,     23770,   2007 )
insert into @tbla values (20 ,     29019,   2007 )
insert into @tbla values (20 ,     29019,   2007 )
insert into @tbla values (20 ,     31839,   2007 )
insert into @tbla values (20 ,     31839,   2007 )
insert into @tbla values (21 ,     31030 ,  2007 )
insert into @tbla values (20 ,     31030 ,  2004 )
insert into @tbla values (20 ,     31693 ,  2005 )
insert into @tbla values (20 ,     30825 ,  2005 )
insert into @tbla values (20 ,     31759 ,  2005 )

select a.id,
count(case when yearid = 2007  then 1 end)  as [Year-07Count],
count(case when yearid = 2004  then 1 end)  as [Year-04Count],
count(case when yearid = 2005  then 1 end)  as [Year-05Count]
from @tbla a
group by a.id

-----------------------------------------------Reply-----------------------------------------------

Hello,
        You can create view for each year. In this view you should use group
by  and count keywords and then you can join this table. It's easy solution
but there are many solutions. May be You can use OLAP programming:)

--
Haydar TUNA
Republic Of Turkey - Ministry of National Education
Education Technology Department Ankara / TURKEY
Web: http://www.haydartuna.net

"ChiWhiteSox" <ChiWhite@discussions.microsoft.com> wrote in message

news:066DC731-B699-4FDF-9D9F-EC8150ADDDED@microsoft.com...

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc