complex sql query
-
Hi All, I am looking for query which needs two group by.. table looks like--- falconid priority affecteditem region 1 1 textbook ny 2 1 database ln 3 2 textbook ln 4 1 database zu 5 2 coin ny 6 2 textbook zu now i want to result sorted by affected item and then count(*) according to region base in set like so that i can count how many ticket opened region vise.. affecteditem ln zu ny textbook 2 1 0 database 1 1 0 coin 0 0 1 Thanks, Abhishek
-
Hi All, I am looking for query which needs two group by.. table looks like--- falconid priority affecteditem region 1 1 textbook ny 2 1 database ln 3 2 textbook ln 4 1 database zu 5 2 coin ny 6 2 textbook zu now i want to result sorted by affected item and then count(*) according to region base in set like so that i can count how many ticket opened region vise.. affecteditem ln zu ny textbook 2 1 0 database 1 1 0 coin 0 0 1 Thanks, Abhishek
-
Hi All, I am looking for query which needs two group by.. table looks like--- falconid priority affecteditem region 1 1 textbook ny 2 1 database ln 3 2 textbook ln 4 1 database zu 5 2 coin ny 6 2 textbook zu now i want to result sorted by affected item and then count(*) according to region base in set like so that i can count how many ticket opened region vise.. affecteditem ln zu ny textbook 2 1 0 database 1 1 0 coin 0 0 1 Thanks, Abhishek
Assuming SQL Server, this article may help. Pivot two or more columns in SQL Server 2005
Never underestimate the power of human stupidity RAH
-
Hi All, I am looking for query which needs two group by.. table looks like--- falconid priority affecteditem region 1 1 textbook ny 2 1 database ln 3 2 textbook ln 4 1 database zu 5 2 coin ny 6 2 textbook zu now i want to result sorted by affected item and then count(*) according to region base in set like so that i can count how many ticket opened region vise.. affecteditem ln zu ny textbook 2 1 0 database 1 1 0 coin 0 0 1 Thanks, Abhishek
Hi, Apologize if you did not understand my problem..I am describing it again.. I have table which having four column,falconid,priority,affected item and region. falconid is primary key,priority shows how much critical is problem,affected item shows which application is affecting and user is facing problem,region shows the different time zone multiple user is raising the tickets from different zone with respect to affected item.that all will save in single table. like below.. falconid priority affecteditem region 1 1 textbook ny 2 1 database ln 3 2 textbook ln 4 1 database zu 5 2 coin ny 6 2 textbook zu Now I want to calculate at the end of the day,that how many ticket(count*) has been raise for the particular affected item from which zone.. in table there are 3 ticket for textbook but from different region I want to calculate the number of ticket raised from different region on affected item wise..like below affecteditem ln zu ny textbook 1 1 1 database 1 1 0 coin 0 0 1
-
Hi, Apologize if you did not understand my problem..I am describing it again.. I have table which having four column,falconid,priority,affected item and region. falconid is primary key,priority shows how much critical is problem,affected item shows which application is affecting and user is facing problem,region shows the different time zone multiple user is raising the tickets from different zone with respect to affected item.that all will save in single table. like below.. falconid priority affecteditem region 1 1 textbook ny 2 1 database ln 3 2 textbook ln 4 1 database zu 5 2 coin ny 6 2 textbook zu Now I want to calculate at the end of the day,that how many ticket(count*) has been raise for the particular affected item from which zone.. in table there are 3 ticket for textbook but from different region I want to calculate the number of ticket raised from different region on affected item wise..like below affecteditem ln zu ny textbook 1 1 1 database 1 1 0 coin 0 0 1
I understand, you want to PIVOT priority for each region by affecteditem. You need a PIVOT query, it is not a simple thing therefore I directed you to an ARTICLE that will help explain and guide you through creating the PIVOT query. There are also some excellent examples in BOL that you might want to work through. I could write the query for you but I already know how to do these and you need to learn, so!
Never underestimate the power of human stupidity RAH
-
I understand, you want to PIVOT priority for each region by affecteditem. You need a PIVOT query, it is not a simple thing therefore I directed you to an ARTICLE that will help explain and guide you through creating the PIVOT query. There are also some excellent examples in BOL that you might want to work through. I could write the query for you but I already know how to do these and you need to learn, so!
Never underestimate the power of human stupidity RAH
Hi Rah, Thanks for the article,I am trying but as I am not the database resource so it will take the time.. I am learning now how pivote works.... but it is urgent for me as i have to demo of project tomarrow,, can u provide me the query on urgent basis..... Thanks in advance, Abhishek
-
Hi Rah, Thanks for the article,I am trying but as I am not the database resource so it will take the time.. I am learning now how pivote works.... but it is urgent for me as i have to demo of project tomarrow,, can u provide me the query on urgent basis..... Thanks in advance, Abhishek
create table #table (affected item varchar, LN int, NY int, Zu int) insert(table) select Affected item,SUM( case region when 'LN" then 1 else 0) LN,SUM( case region when "NY" then 1 else 0) NY,SUM( case region when 'ZU" then 1 else 0) ZU from tablename groupby affected item please advice if i am wrong? Thanks, Abhishek