SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SELECT ALL Columns values with Group By clause		  New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

deepak_Dotnet
Starting Member

32 Posts
Posted - 06/04/2007 :  10:18:14  Show Profile  Reply with Quote
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 values

SELECT *,COUNT(Distinct(ID)) FROM vw_Registrattion GROUP BY year

---Throwing error

So how can get all columns values and also count of records when i use "GROUP BY" clause..?
Edited by - deepak_Dotnet on 06/04/2007 10:18:57

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 06/04/2007 :  10:24:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
By putting all column names in the GROUP BY statement.
But I think there must be a simpler way to do this...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts
Posted - 06/04/2007 :  10:38:41  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
How about some sample data and what you are trying to return?

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 06/04/2007 :  10:58:10  Show Profile  Reply with Quote
Why are you grouping by Year...what is the desired result set?

And SELECT * is not a good idea in the first place



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22460 Posts
Posted - 06/04/2007 :  11:04:14  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Read about Group by in sql server help file to know how it works
As said, post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

deepak_Dotnet
Starting Member

32 Posts
Posted - 06/05/2007 :  05:07:02  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

Read about Group by in sql server help file to know how it works
As said, post some sample data with expected result

Madhivanan

Failing 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,REGION


So 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..?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 06/05/2007 :  05:16:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 Larsson
Helsingborg, Sweden
Go to Top of Page

deepak_Dotnet
Starting Member

32 Posts
Posted - 06/05/2007 :  05:41:31  Show Profile  Reply with Quote
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 Larsson
Helsingborg, Sweden


HI,
View Name : vw_Person

(Here i reduced the number columns to few columns)
(PK)
ID |NAME | ADDRESS | IDENTITYNUMBER | ISSPECIAL | CREATEDYEAR | REGION

ID01| xyz| USA | ASDQ001 | 1 | 1999 | NY
ID05| ABC| UK | FGH005 | 0 | 1989 | London
ID76| PQR| UAE | JHUY78 | 1 | 2001 | Dubai
ID87| WXY| AUS | JHGE098 | 1 | 1999 | Sydney
ID76| TUV| INDIA | JHGR654 | 1 | 1999 | Balore
ID55| EFG| CHINA | KJHG78 | 1 | 2001 | Shangai
ID99| JHY| FRANCE | khf45 | 0 | 1999 | paris

So 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 NY
ID87 WXY AUS JHGE098 1 1999 Sydney
ID76 TUV INDIA JHGR654 1 1999 Balore
ID99 JHY FRANCE khf45 0 1999 paris
ID76 PQR UAE JHUY78 1 2001 Dubai
ID55 EFG CHINA KJHG78 1 2001 Shangai
ID05 ABC UK FGH005 0 1989 London

So how to get all column value of view group by just single coulmn.....
Edited by - deepak_Dotnet on 06/05/2007 05:49:13
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts
Posted - 06/05/2007 :  05:45:30  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
This is not GROUPing, it's called filtering and can be achieved using WHERE clause.

For example,
WHERE CREATEDYEAR = 1999


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

deepak_Dotnet
Starting Member

32 Posts
Posted - 06/05/2007 :  05:50:52  Show Profile  Reply with Quote
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 Athalye
India.
"The IMPOSSIBLE is often UNTRIED"


Hi,
please read my query ...
Edited by - deepak_Dotnet on 06/05/2007 05:51:10
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 06/05/2007 :  06:45:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT CAST(ID AS VARCHAR) + NAME + ADDRESS + IDENTITYNUMBER + CAST(ISSPECIAL AS VARCHAR) + CAST(CREATEDYEAR AS VARCHAR) + REGION
FROM ...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22460 Posts
Posted - 06/05/2007 :  09:19:35  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 data

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts
Posted - 06/05/2007 :  10:00:31  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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 Larsson
Helsingborg, Sweden


HI,
View Name : vw_Person

(Here i reduced the number columns to few columns)
(PK)
ID |NAME | ADDRESS | IDENTITYNUMBER | ISSPECIAL | CREATEDYEAR | REGION

ID01| xyz| USA | ASDQ001 | 1 | 1999 | NY
ID05| ABC| UK | FGH005 | 0 | 1989 | London
ID76| PQR| UAE | JHUY78 | 1 | 2001 | Dubai
ID87| WXY| AUS | JHGE098 | 1 | 1999 | Sydney
ID76| TUV| INDIA | JHGR654 | 1 | 1999 | Balore
ID55| EFG| CHINA | KJHG78 | 1 | 2001 | Shangai
ID99| JHY| FRANCE | khf45 | 0 | 1999 | paris

So 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 NY
ID87 WXY AUS JHGE098 1 1999 Sydney
ID76 TUV INDIA JHGR654 1 1999 Balore
ID99 JHY FRANCE khf45 0 1999 paris
ID76 PQR UAE JHUY78 1 2001 Dubai
ID55 EFG CHINA KJHG78 1 2001 Shangai
ID05 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?

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

deepak_Dotnet
Starting Member

32 Posts
Posted - 06/05/2007 :  10:19:17  Show Profile  Reply with Quote
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 Larsson
Helsingborg, Sweden


HI,
View Name : vw_Person

(Here i reduced the number columns to few columns)
(PK)
ID |NAME | ADDRESS | IDENTITYNUMBER | ISSPECIAL | CREATEDYEAR | REGION

ID01| xyz| USA | ASDQ001 | 1 | 1999 | NY
ID05| ABC| UK | FGH005 | 0 | 1989 | London
ID76| PQR| UAE | JHUY78 | 1 | 2001 | Dubai
ID87| WXY| AUS | JHGE098 | 1 | 1999 | Sydney
ID76| TUV| INDIA | JHGR654 | 1 | 1999 | Balore
ID55| EFG| CHINA | KJHG78 | 1 | 2001 | Shangai
ID99| JHY| FRANCE | khf45 | 0 | 1999 | paris

So 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 NY
ID87 WXY AUS JHGE098 1 1999 Sydney
ID76 TUV INDIA JHGR654 1 1999 Balore
ID99 JHY FRANCE khf45 0 1999 paris
ID76 PQR UAE JHUY78 1 2001 Dubai
ID55 EFG CHINA KJHG78 1 2001 Shangai
ID05 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?

- Jeff
http://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...
Edited by - deepak_Dotnet on 06/05/2007 10:21:44
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22460 Posts
Posted - 06/05/2007 :  10:26:23  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Which report are you using?
With the help of formula you can concatenate data

ex in Crystal Report

{col1}+" "+{col2}+" "+{col3}+" "+{col4}+" "+...{colN}

Also you need to handle NULL values

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

deepak_Dotnet
Starting Member

32 Posts
Posted - 06/06/2007 :  03:23:07  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

Which report are you using?
With the help of formula you can concatenate data

ex in Crystal Report

{col1}+" "+{col2}+" "+{col3}+" "+{col4}+" "+...{colN}

Also you need to handle NULL values

Madhivanan

Failing 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 ..
Go to Top of Page

deepak_Dotnet
Starting Member

32 Posts
Posted - 06/07/2007 :  01:40:49  Show Profile  Reply with Quote
Hi,
No body is having solution for this ...?
Go to Top of Page

Koji Matsumura
Posting Yak Master

141 Posts
Posted - 06/07/2007 :  01:54:08  Show Profile  Reply with Quote
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 Larsson
Helsingborg, Sweden


HI,
View Name : vw_Person

(Here i reduced the number columns to few columns)
(PK)
ID |NAME | ADDRESS | IDENTITYNUMBER | ISSPECIAL | CREATEDYEAR | REGION

ID01| xyz| USA | ASDQ001 | 1 | 1999 | NY
ID05| ABC| UK | FGH005 | 0 | 1989 | London
ID76| PQR| UAE | JHUY78 | 1 | 2001 | Dubai
ID87| WXY| AUS | JHGE098 | 1 | 1999 | Sydney
ID76| TUV| INDIA | JHGR654 | 1 | 1999 | Balore
ID55| EFG| CHINA | KJHG78 | 1 | 2001 | Shangai
ID99| JHY| FRANCE | khf45 | 0 | 1999 | paris

So 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 NY
ID87 WXY AUS JHGE098 1 1999 Sydney
ID76 TUV INDIA JHGR654 1 1999 Balore
ID99 JHY FRANCE khf45 0 1999 paris
ID76 PQR UAE JHUY78 1 2001 Dubai
ID55 EFG CHINA KJHG78 1 2001 Shangai
ID05 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
Go to Top of Page

Ifor
Constraint Violating Yak Guru

475 Posts
Posted - 06/07/2007 :  06:52:28  Show Profile  Reply with Quote
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.IDsInYear
FROM 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.CREATEDYEAR
ORDER BY P.CREATEDYEAR
Edited by - Ifor on 06/07/2007 07:56:32
Go to Top of Page

deepak_Dotnet
Starting Member

32 Posts
Posted - 06/07/2007 :  06:57:06  Show Profile  Reply with Quote
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 Larsson
Helsingborg, Sweden


HI,
View Name : vw_Person

(Here i reduced the number columns to few columns)
(PK)
ID |NAME | ADDRESS | IDENTITYNUMBER | ISSPECIAL | CREATEDYEAR | REGION

ID01| xyz| USA | ASDQ001 | 1 | 1999 | NY
ID05| ABC| UK | FGH005 | 0 | 1989 | London
ID76| PQR| UAE | JHUY78 | 1 | 2001 | Dubai
ID87| WXY| AUS | JHGE098 | 1 | 1999 | Sydney
ID76| TUV| INDIA | JHGR654 | 1 | 1999 | Balore
ID55| EFG| CHINA | KJHG78 | 1 | 2001 | Shangai
ID99| JHY| FRANCE | khf45 | 0 | 1999 | paris

So 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 NY
ID87 WXY AUS JHGE098 1 1999 Sydney
ID76 TUV INDIA JHGR654 1 1999 Balore
ID99 JHY FRANCE khf45 0 1999 paris
ID76 PQR UAE JHUY78 1 2001 Dubai
ID55 EFG CHINA KJHG78 1 2001 Shangai
ID05 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| NY
ID05| ABC| UK | FGH005 | 0 | 1989 | LON_12|London
ID76| PQR| UAE | JHUY78 | 1 | 2001 | DUB_23 |Dubai
ID87| WXY| AUS | JHGE098 | 1 | 1999 | SYD_04|Sydney
ID76| TUV| INDIA | JHGR654 | 1 | 1999 | BLR_66|Balore
ID55| EFG| CHINA | KJHG78 | 1 | 2001 | LON_12| London
ID79| JHY| GERMANY| khf45 | 0 | 1999 | DUB_23 |Dubai
ID69| HHH| AFRICA | khf45 | 0 | 1999 | DUB_23 |Dubai
ID39| YHY| UAE | khf45 | 0 | 2001 | DUB_23 |Dubai
ID29| IHY| IRAN | khf45 | 0 | 2002 | DUB_23 |Dubai

REGID = Registration ID

I 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 YEAR
so 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)State


I hope i cleared explained my query ....
Edited by - deepak_Dotnet on 06/07/2007 07:02:38
Go to Top of Page

Koji Matsumura
Posting Yak Master

141 Posts
Posted - 06/07/2007 :  07:09:34  Show Profile  Reply with Quote
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| NY
ID05| ABC| UK | FGH005 | 0 | 1989 | LON_12|London
ID76| PQR| UAE | JHUY78 | 1 | 2001 | DUB_23 |Dubai
ID87| WXY| AUS | JHGE098 | 1 | 1999 | SYD_04|Sydney
ID76| TUV| INDIA | JHGR654 | 1 | 1999 | BLR_66|Balore
ID55| EFG| CHINA | KJHG78 | 1 | 2001 | LON_12| London
ID79| JHY| GERMANY| khf45 | 0 | 1999 | DUB_23 |Dubai
ID69| HHH| AFRICA | khf45 | 0 | 1999 | DUB_23 |Dubai
ID39| YHY| UAE | khf45 | 0 | 2001 | DUB_23 |Dubai
ID29| IHY| IRAN | khf45 | 0 | 2002 | DUB_23 |Dubai

REGID = Registration ID

I 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 YEAR
so 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)State


I hope i cleared explained my query ....



SELECT Year, State, COUNT(*)
FROM YourTable
GROUP BY Year, State
ORDER BY Year, State

If you don't know the expected result
how can we help you?
Maybe you should post the exact requirement
from your client.

K. Matsumura
Edited by - Koji Matsumura on 06/07/2007 07:13:54
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000