Author |
Topic |
deepak_Dotnet
Starting Member
32 Posts |
Posted - 2007-06-04 : 10:18:14
|
HI, I have a view where i have joined 3 or 4 tables.I have column called "year"..I want to get all columns values of view grouped by "year".So i used "GROUP BY" clause on year column.But i am getting error and unable to retriev column valuesSELECT *,COUNT(Distinct(ID)) FROM vw_Registrattion GROUP BY year---Throwing errorSo how can get all columns values and also count of records when i use "GROUP BY" clause..? |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-06-04 : 10:24:01
|
By putting all column names in the GROUP BY statement.But I think there must be a simpler way to do this...Peter LarssonHelsingborg, Sweden |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
X002548
Not Just a Number
15586 Posts |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-04 : 11:04:14
|
Read about Group by in sql server help file to know how it worksAs said, post some sample data with expected resultMadhivananFailing to plan is Planning to fail |
|
|
deepak_Dotnet
Starting Member
32 Posts |
Posted - 2007-06-05 : 05:07:02
|
quote: Originally posted by madhivanan Read about Group by in sql server help file to know how it worksAs said, post some sample data with expected resultMadhivananFailing to plan is Planning to fail
Hi Madhivanan, I know very well how GROUP BY Clause work.To get all column values along GROUP BY ,we have to use all column values in GROUP Clause,But which i don;'t want to do ,bcoz it will give me undesired result.I put this query in forum seeking for alternate soultion from SQL experts.I have view which is joined using 3 tables.So my view has following columns : ID, NAME, ADDRESS,IDENTITYNUMBER, ISSPECIAL,CREATEDYEAR ,MODIFIEDDATE,REGIONSo here i want to retireve all columns values group by CREATEDYEAR only.So is there any way to get all columns values group by single column..? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-06-05 : 05:16:30
|
Please post some proper sample data and your expected output.I am sure some of us will assist you in writing a query that fulfills your need.Peter LarssonHelsingborg, Sweden |
|
|
deepak_Dotnet
Starting Member
32 Posts |
Posted - 2007-06-05 : 05:41:31
|
quote: Originally posted by Peso Please post some proper sample data and your expected output.I am sure some of us will assist you in writing a query that fulfills your need.Peter LarssonHelsingborg, Sweden
HI,View Name : vw_Person(Here i reduced the number columns to few columns)(PK) ID |NAME | ADDRESS | IDENTITYNUMBER | ISSPECIAL | CREATEDYEAR | REGIONID01| xyz| USA | ASDQ001 | 1 | 1999 | NYID05| ABC| UK | FGH005 | 0 | 1989 | London ID76| PQR| UAE | JHUY78 | 1 | 2001 | DubaiID87| WXY| AUS | JHGE098 | 1 | 1999 | SydneyID76| TUV| INDIA | JHGR654 | 1 | 1999 | BaloreID55| EFG| CHINA | KJHG78 | 1 | 2001 | ShangaiID99| JHY| FRANCE | khf45 | 0 | 1999 | parisSo here i wann group by year .So i wann get all column value from above view group by year.If i group by year i should get result like this ...ID01 xyz USA ASDQ001 1 1999 NYID87 WXY AUS JHGE098 1 1999 SydneyID76 TUV INDIA JHGR654 1 1999 BaloreID99 JHY FRANCE khf45 0 1999 parisID76 PQR UAE JHUY78 1 2001 DubaiID55 EFG CHINA KJHG78 1 2001 ShangaiID05 ABC UK FGH005 0 1989 London So how to get all column value of view group by just single coulmn..... |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-06-05 : 05:45:30
|
This is not GROUPing, it's called filtering and can be achieved using WHERE clause.For example,WHERE CREATEDYEAR = 1999 Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
deepak_Dotnet
Starting Member
32 Posts |
Posted - 2007-06-05 : 05:50:52
|
quote: Originally posted by harsh_athalye This is not GROUPing, it's called filtering and can be achieved using WHERE clause.For example,WHERE CREATEDYEAR = 1999 Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED"
Hi, please read my query ... |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-06-05 : 06:45:36
|
SELECT CAST(ID AS VARCHAR) + NAME + ADDRESS + IDENTITYNUMBER + CAST(ISSPECIAL AS VARCHAR) + CAST(CREATEDYEAR AS VARCHAR) + REGIONFROM ...Peter LarssonHelsingborg, Sweden |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-05 : 09:19:35
|
Where do you want to show the concatenated data?Note that if you want to retreive some values, then you need to again split the dataMadhivananFailing to plan is Planning to fail |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-06-05 : 10:00:31
|
quote: Originally posted by deepak_Dotnet
quote: Originally posted by Peso Please post some proper sample data and your expected output.I am sure some of us will assist you in writing a query that fulfills your need.Peter LarssonHelsingborg, Sweden
HI,View Name : vw_Person(Here i reduced the number columns to few columns)(PK) ID |NAME | ADDRESS | IDENTITYNUMBER | ISSPECIAL | CREATEDYEAR | REGIONID01| xyz| USA | ASDQ001 | 1 | 1999 | NYID05| ABC| UK | FGH005 | 0 | 1989 | London ID76| PQR| UAE | JHUY78 | 1 | 2001 | DubaiID87| WXY| AUS | JHGE098 | 1 | 1999 | SydneyID76| TUV| INDIA | JHGR654 | 1 | 1999 | BaloreID55| EFG| CHINA | KJHG78 | 1 | 2001 | ShangaiID99| JHY| FRANCE | khf45 | 0 | 1999 | parisSo here i wann group by year .So i wann get all column value from above view group by year.If i group by year i should get result like this ...ID01 xyz USA ASDQ001 1 1999 NYID87 WXY AUS JHGE098 1 1999 SydneyID76 TUV INDIA JHGR654 1 1999 BaloreID99 JHY FRANCE khf45 0 1999 parisID76 PQR UAE JHUY78 1 2001 DubaiID55 EFG CHINA KJHG78 1 2001 ShangaiID05 ABC UK FGH005 0 1989 London So how to get all column value of view group by just single coulmn.....
Are you saying that you want to combine all of your columns into 1 single column??? Why would you want to do this? you are making things much more complicated on yourself! Just return the raw data you need and handle formatting wherever the data is processed. Where are you returning these results to? A web page, a report, etc?- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
deepak_Dotnet
Starting Member
32 Posts |
Posted - 2007-06-05 : 10:19:17
|
quote: Originally posted by jsmith8858
quote: Originally posted by deepak_Dotnet
quote: Originally posted by Peso Please post some proper sample data and your expected output.I am sure some of us will assist you in writing a query that fulfills your need.Peter LarssonHelsingborg, Sweden
HI,View Name : vw_Person(Here i reduced the number columns to few columns)(PK) ID |NAME | ADDRESS | IDENTITYNUMBER | ISSPECIAL | CREATEDYEAR | REGIONID01| xyz| USA | ASDQ001 | 1 | 1999 | NYID05| ABC| UK | FGH005 | 0 | 1989 | London ID76| PQR| UAE | JHUY78 | 1 | 2001 | DubaiID87| WXY| AUS | JHGE098 | 1 | 1999 | SydneyID76| TUV| INDIA | JHGR654 | 1 | 1999 | BaloreID55| EFG| CHINA | KJHG78 | 1 | 2001 | ShangaiID99| JHY| FRANCE | khf45 | 0 | 1999 | parisSo here i wann group by year .So i wann get all column value from above view group by year.If i group by year i should get result like this ...ID01 xyz USA ASDQ001 1 1999 NYID87 WXY AUS JHGE098 1 1999 SydneyID76 TUV INDIA JHGR654 1 1999 BaloreID99 JHY FRANCE khf45 0 1999 parisID76 PQR UAE JHUY78 1 2001 DubaiID55 EFG CHINA KJHG78 1 2001 ShangaiID05 ABC UK FGH005 0 1989 London So how to get all column value of view group by just single coulmn.....
Are you saying that you want to combine all of your columns into 1 single column??? Why would you want to do this? you are making things much more complicated on yourself! Just return the raw data you need and handle formatting wherever the data is processed. Where are you returning these results to? A web page, a report, etc?- Jeffhttp://weblogs.sqlteam.com/JeffS
Hi , UR right ,i wann retrive values for my reports ( from my view) ..so i CANNOT combine all columns values as MR. peso has suggested me ...so plz guys suggest me something for this ...Infact ALONG with retrieving all column values ,i wann use count(ID) as well to get count of ID group by Year... |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-05 : 10:26:23
|
Which report are you using?With the help of formula you can concatenate dataex in Crystal Report{col1}+" "+{col2}+" "+{col3}+" "+{col4}+" "+...{colN}Also you need to handle NULL valuesMadhivananFailing to plan is Planning to fail |
|
|
deepak_Dotnet
Starting Member
32 Posts |
Posted - 2007-06-06 : 03:23:07
|
quote: Originally posted by madhivanan Which report are you using?With the help of formula you can concatenate dataex in Crystal Report{col1}+" "+{col2}+" "+{col3}+" "+{col4}+" "+...{colN}Also you need to handle NULL valuesMadhivananFailing to plan is Planning to fail
HI, i am using sql 2005 reporting service ..and creating dataset using stored procedure for generating reports.So i should manage to get all coulmn values from view group by year...I cann't use concentated values ...any soultion for my problme .. |
|
|
deepak_Dotnet
Starting Member
32 Posts |
Posted - 2007-06-07 : 01:40:49
|
Hi, No body is having solution for this ...? |
|
|
Koji Matsumura
Posting Yak Master
141 Posts |
Posted - 2007-06-07 : 01:54:08
|
quote: Originally posted by deepak_Dotnet
quote: Originally posted by Peso Please post some proper sample data and your expected output.I am sure some of us will assist you in writing a query that fulfills your need.Peter LarssonHelsingborg, Sweden
HI,View Name : vw_Person(Here i reduced the number columns to few columns)(PK) ID |NAME | ADDRESS | IDENTITYNUMBER | ISSPECIAL | CREATEDYEAR | REGIONID01| xyz| USA | ASDQ001 | 1 | 1999 | NYID05| ABC| UK | FGH005 | 0 | 1989 | London ID76| PQR| UAE | JHUY78 | 1 | 2001 | DubaiID87| WXY| AUS | JHGE098 | 1 | 1999 | SydneyID76| TUV| INDIA | JHGR654 | 1 | 1999 | BaloreID55| EFG| CHINA | KJHG78 | 1 | 2001 | ShangaiID99| JHY| FRANCE | khf45 | 0 | 1999 | parisSo here i wann group by year .So i wann get all column value from above view group by year.If i group by year i should get result like this ...ID01 xyz USA ASDQ001 1 1999 NYID87 WXY AUS JHGE098 1 1999 SydneyID76 TUV INDIA JHGR654 1 1999 BaloreID99 JHY FRANCE khf45 0 1999 parisID76 PQR UAE JHUY78 1 2001 DubaiID55 EFG CHINA KJHG78 1 2001 ShangaiID05 ABC UK FGH005 0 1989 London So how to get all column value of view group by just single coulmn.....
Your example is just selecting all columns and rows in different order.No COUNT is shown.We (or at least I) don't understand your requiremtns.Please post DDL, sample data, and expected result in proper manner.K. Matsumura |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2007-06-07 : 06:52:28
|
Maybe this is what is wanted - or maybe not?SELECT P.[ID], P.[NAME], P.ADDRESS, P.IDENTITYNUMBER ,P.ISSPECIAL, P.CREATEDYEAR, P.REGION, D.IDsInYearFROM vw_Person P JOIN ( SELECT P1.CREATEDYEAR, COUNT(DISTINCT P1.[ID]) AS IDsInYear FROM vw_Person P1 GROUP BY P1.CREATEDYEAR ) D ON P.CREATEDYEAR = D.CREATEDYEARORDER BY P.CREATEDYEAR |
|
|
deepak_Dotnet
Starting Member
32 Posts |
Posted - 2007-06-07 : 06:57:06
|
quote: Originally posted by Koji Matsumura
quote: Originally posted by deepak_Dotnet
quote: Originally posted by Peso Please post some proper sample data and your expected output.I am sure some of us will assist you in writing a query that fulfills your need.Peter LarssonHelsingborg, Sweden
HI,View Name : vw_Person(Here i reduced the number columns to few columns)(PK) ID |NAME | ADDRESS | IDENTITYNUMBER | ISSPECIAL | CREATEDYEAR | REGIONID01| xyz| USA | ASDQ001 | 1 | 1999 | NYID05| ABC| UK | FGH005 | 0 | 1989 | London ID76| PQR| UAE | JHUY78 | 1 | 2001 | DubaiID87| WXY| AUS | JHGE098 | 1 | 1999 | SydneyID76| TUV| INDIA | JHGR654 | 1 | 1999 | BaloreID55| EFG| CHINA | KJHG78 | 1 | 2001 | ShangaiID99| JHY| FRANCE | khf45 | 0 | 1999 | parisSo here i wann group by year .So i wann get all column value from above view group by year.If i group by year i should get result like this ...ID01 xyz USA ASDQ001 1 1999 NYID87 WXY AUS JHGE098 1 1999 SydneyID76 TUV INDIA JHGR654 1 1999 BaloreID99 JHY FRANCE khf45 0 1999 parisID76 PQR UAE JHUY78 1 2001 DubaiID55 EFG CHINA KJHG78 1 2001 ShangaiID05 ABC UK FGH005 0 1989 London So how to get all column value of view group by just single coulmn.....
Your example is just selecting all columns and rows in different order.No COUNT is shown.We (or at least I) don't understand your requiremtns.Please post DDL, sample data, and expected result in proper manner.K. Matsumura
Hi Matsumura , For ur Reference ,i am going to explain u again ....I have view which display info like this (Here i reduced the number columns to few columns)(PK) REGID |NAME |ADDRESS|IDENTITYNUMBER|ISSPECIAL|CREATEDYEAR|STATEID|STATE|ID01| xyz| USA | ASDQ001 | 1 | 1999 |NY_01| NYID05| ABC| UK | FGH005 | 0 | 1989 | LON_12|London ID76| PQR| UAE | JHUY78 | 1 | 2001 | DUB_23 |DubaiID87| WXY| AUS | JHGE098 | 1 | 1999 | SYD_04|SydneyID76| TUV| INDIA | JHGR654 | 1 | 1999 | BLR_66|BaloreID55| EFG| CHINA | KJHG78 | 1 | 2001 | LON_12| LondonID79| JHY| GERMANY| khf45 | 0 | 1999 | DUB_23 |DubaiID69| HHH| AFRICA | khf45 | 0 | 1999 | DUB_23 |DubaiID39| YHY| UAE | khf45 | 0 | 2001 | DUB_23 |DubaiID29| IHY| IRAN | khf45 | 0 | 2002 | DUB_23 |DubaiREGID = Registration IDI DOn;t know the expected result ,as i need to build the stored procedure/query based on the Criteria given to me I want to retrieve Number of Registrations (i.e COUNT(REGID)) for each STATE and each YEARso Now GROUP BY Condition is GROUP BY 1)STATE 2) CREATEDYEAR (BOTH).....So when i use the query/SP ,i should get 3 columns in result 1)No. of Registration 2)Year 3)StateI hope i cleared explained my query .... |
|
|
Koji Matsumura
Posting Yak Master
141 Posts |
Posted - 2007-06-07 : 07:09:34
|
quote: Hi Matsumura , For ur Reference ,i am going to explain u again ....I have view which display info like this (Here i reduced the number columns to few columns)(PK) REGID |NAME |ADDRESS|IDENTITYNUMBER|ISSPECIAL|CREATEDYEAR|STATEID|STATE|ID01| xyz| USA | ASDQ001 | 1 | 1999 |NY_01| NYID05| ABC| UK | FGH005 | 0 | 1989 | LON_12|London ID76| PQR| UAE | JHUY78 | 1 | 2001 | DUB_23 |DubaiID87| WXY| AUS | JHGE098 | 1 | 1999 | SYD_04|SydneyID76| TUV| INDIA | JHGR654 | 1 | 1999 | BLR_66|BaloreID55| EFG| CHINA | KJHG78 | 1 | 2001 | LON_12| LondonID79| JHY| GERMANY| khf45 | 0 | 1999 | DUB_23 |DubaiID69| HHH| AFRICA | khf45 | 0 | 1999 | DUB_23 |DubaiID39| YHY| UAE | khf45 | 0 | 2001 | DUB_23 |DubaiID29| IHY| IRAN | khf45 | 0 | 2002 | DUB_23 |DubaiREGID = Registration IDI DOn;t know the expected result ,as i need to build the stored procedure/query based on the Criteria given to me I want to retrieve Number of Registrations (i.e COUNT(REGID)) for each STATE and each YEARso Now GROUP BY Condition is GROUP BY 1)STATE 2) CREATEDYEAR (BOTH).....So when i use the query/SP ,i should get 3 columns in result 1)No. of Registration 2)Year 3)StateI hope i cleared explained my query ....
SELECT Year, State, COUNT(*)FROM YourTableGROUP BY Year, StateORDER BY Year, StateIf you don't know the expected resulthow can we help you?Maybe you should post the exact requirementfrom your client.K. Matsumura |
|
|
Previous Page&nsp;
Next Page
|