Sql Server Programming :: help with a simple query??


	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... - Hide quoted text - > 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 On 3?15?, ??7?53?, "Nigel A" <n@discussion.microsoft.com> wrote: - Hide quoted text - > 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... > > 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- ??????? - > - ??????? - and CASE statement :) -----------------------------------------------Reply----------------------------------------------- On Mar 15, 3:37 am, ChiWhiteSox - Hide quoted text - <ChiWhite@discussions.microsoft.com> wrote: > 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 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... - Hide quoted text - > 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