Group By - SQL Server Forums

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)

Group By

Author

Topic

micky--4444
Starting Member

19 Posts

Posted - 2007-01-21 : 10:56:30

Below is my SQL query

SELECT tblCategoryAccessed.MemberID, tblCategoryAccessed.Dateandtime, tblCategoryAccessed.CategoryAccessed,
tblMembers.FirstName, tblMembers.Surname, tblMembers.ID, tblMembers.MembershipOption FROM
tblMembers INNER JOIN tblCategoryAccessed ON tblMembers.ID=tblCategoryAccessed.MemberID

I'd like to know how to only select unique MemberID's based on the members.

I'd appreciate any help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-21 : 13:58:02

1)
select distinct memberid
from (...your query above here...) as d

2)
SELECT DISTINCT tblCategoryAccessed.MemberID FROM tblCategoryAccessed

3)
SELECT DISTINCT tblMembers.MemberID FROM tblMembers

4)
SELECT MemberID FROM tblCategoryAccessed
UNION
SELECT MemberID FROM tblMembers

Peter Larsson
Helsingborg, Sweden

micky--4444
Starting Member

19 Posts

Posted - 2007-01-21 : 15:29:12

Hi,
If I use as my SQL query

sSQL="SELECT Distinct tblMembers.ID As dddd, tblMembers.MembershipOption, tblMembers.FirstName, tblMembers.Surname, tblCategoryAccessed.MemberID, tblCategoryAccessed.Dateandtime, tblCategoryAccessed.CategoryAccessed" & _
" FROM " & _
" tblMembers INNER JOIN tblCategoryAccessed ON tblMembers.ID=tblCategoryAccessed.MemberID"

And then loop through the returned recordset, I get the same ID quite a bit?

Do I need some sort of group by? rather than the Distinct Keyword.

thanks,
Michael.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 00:52:21

sSQL = "SELECT tblCategoryAccessed.MemberID, tblCategoryAccessed.Dateandtime, tblCategoryAccessed.CategoryAccessed,
tblMembers.FirstName, tblMembers.Surname, tblMembers.ID, tblMembers.MembershipOption FROM
tblMembers INNER JOIN tblCategoryAccessed ON tblMembers.ID=tblCategoryAccessed.MemberID"

sSQLcmd = "SELECT DISTINCT MemberID FROM (" & sSQL & ") as d"

con.execute(ssqlcmd)

Peter Larsson
Helsingborg, Sweden

micky--4444
Starting Member

19 Posts

Posted - 2007-01-23 : 04:51:59

Hi Peter,
This works great when it's

sSQL = "SELECT tblCategoryAccessed.MemberID, tblCategoryAccessed.Dateandtime, tblCategoryAccessed.CategoryAccessed,
tblMembers.FirstName, tblMembers.Surname, tblMembers.ID, tblMembers.MembershipOption FROM
tblMembers INNER JOIN tblCategoryAccessed ON tblMembers.ID=tblCategoryAccessed.MemberID"

sSQLcmd = "SELECT DISTINCT MemberID Ad d, tblMembers.Surname, tblCategoryAccessed.CategoryAccessed FROM (" & sSQL & ") as d"

con.execute(ssqlcmd)

But if I include tblCategoryAccessed.DateandTime I get all the MemberID's again, I guess due to the Distinct Keyword. How could I Keep it Distinct for - MemberID Ad d, tblMembers.Surname, tblCategoryAccessed.CategoryAccessed - and still list the tblCategoryAccessed.DateandTime

Hope this makes sense.
thanks,
Michael.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-23 : 05:23:01

Which DateAndTime do you want to show? MAXIMUM value for the combination of MemberID, Surname and CategoryAccessed?

Do you have ANY idea what DISTINCT is or does/work?

Please provide some proper sample data, and your expected output based on the sample data you provide.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-23 : 05:23:46

Or do you simply mean that you want to remove the time part from the DateAndTime column, and only show the date?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-23 : 05:25:22

Or do you want to suppress the following MemberID's in the report you have?
Then you have to do that with the option "Hide duplicate information" in the report writer.

Peter Larsson
Helsingborg, Sweden

micky--4444
Starting Member

19 Posts

Posted - 2007-01-23 : 06:26:49

Hi Peso,
The Distinct keyword works with as a combination of fields, so the more fields you have obviously the less uniqueness. I mistakenly believed it to just work on one field.

Here's is 2 screenshots of the records returned.

Adding the additional 'DateandTime' field in figure 2 obviously makes the rows less distinct.

http://mwall283x.brinkster.net/images/figure1.jpg

http://mwall283x.brinkster.net/images/figure2.jpg

What I am trying to achieve is just a row for each member or a row for each member and each different category combination.

thanks,
Michael.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-23 : 06:42:16

Then the above posted code will work.

Peter Larsson
Helsingborg, Sweden

micky--4444
Starting Member

19 Posts

Posted - 2007-01-23 : 07:40:33

Hi Peso,
What I'm trying to achieve is only one row for each 'DDDD'.
If you look at http://mwall283x.brinkster.net/images/figure2.jpg you'll notice that there is multiple memberIDs of '10'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-23 : 07:49:29

Did you not understand when I asked you "Or do you simply mean that you want to remove the time part from the DateAndTime column, and only show the date?" ?
If you remove the time information from the DateAndTime column, you will get fewer records with DISTINCT!

Try this

SELECT DISTINCT	m.ID,
		m.Surname,
		ca.CategoryAccessed,
		DATEADD(day, DATEDIFF(day, 0, ca.DateAndTime) ,0) AS DateAndTime
FROM		tblMembers AS m
INNER JOIN	tblCategoryAccessed AS ca ON ca.MemberID = m.ID

Peter Larsson
Helsingborg, Sweden

micky--4444
Starting Member

19 Posts

Posted - 2007-01-23 : 08:30:18

cheers Peso you've been a great help.
Initially I thought I might be looking at using Group By, but I wasn't using any aggregate functions so that appeared to be ruled out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources