Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SELECT ALL Columns values with Group By clause

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 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..?

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 Larsson
Helsingborg, Sweden
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-04 : 10:38:41
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 - 2007-06-04 : 10:58:10
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

22864 Posts

Posted - 2007-06-04 : 11:04:14
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 - 2007-06-05 : 05:07:02
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

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 Larsson
Helsingborg, Sweden
Go to Top of Page

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

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 Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

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 Athalye
India.
"The IMPOSSIBLE is often UNTRIED"



Hi,
please read my query ...
Go to Top of Page

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) + REGION
FROM ...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

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 data

Madhivanan

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

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

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 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 - 2007-06-06 : 03:23:07
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 - 2007-06-07 : 01:40:49
Hi,
No body is having solution for this ...?
Go to Top of Page

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

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

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| 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
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -