GROUP BY Not Working

Hi All,
I have a problem trying to write a query with group by and I still get records for the same User ID twice, I think it's just a simple thing that I cannot see.

Here's the query:
SELECT     s.UserID AS [User ID], s.fldDate AS DATE, s.fldAction AS Action, Data.fldName AS First, Data.fldSurname as Last
FROM         Stats s RIGHT OUTER JOIN
                      Data ON s.UserID = Data.ID
GROUP BY s.UserID, s.fldDate, s.fldAction,Data.fldCountry, Data.fldName, Data.fldSurname
HAVING      (s.fldDate BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND CONVERT(DATETIME, '2007-05-05 00:00:00', 102)) AND
                      (Data.fldCountry LIKE '%')
ORDER BY s.fldDate, Data.fldCountry

and I get something like:
     User ID   Date           Action  First     Last
    ---------   -------          --------  -----    -----------
       3385      5/1/2007      0      Dan      Goldman
      3385      5/1/2007      1      Dan      Goldman
      3385      5/2/2007      2      Dan      Goldman
      3385      5/2/2007      3      Dan      Goldman
      3377      5/2/2007      2      Lone      Storie
      3377      5/2/2007      3      Lone      Storie
      3387      5/2/2007      0      James  Trausti
      3387      5/2/2007      1      James Trausti
      3386      5/2/2007      0      Rogo   Ldwvlien

and the User IDs are not grouped.
AdiAsked:
Who is Participating?
 
cmgarnettCommented:
Its along these lines but using your table and field names rather than mine. My Site record has many Product records for each site, so I think its the same sort of thing that your tying to do.

This is my attempt at using your table and field names but of course I can't check my syntax.

SELECT s.UserID AS [User ID], MAX(s.fldDate) AS DATE, (SELECT TOP 1 fldAction FROM Stats WHERE UserID = s.UserID ORDER BY s.fldDate DESC) AS Action, Data.fldName, Data.fldSurname
FROM Stats s
RIGHT OUTER JOIN Data ON s.UserID = Data.ID
WHERE s.fldDate BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND CONVERT(DATETIME, '2007-05-05 00:00:00', 102)
AND (Data.fldCountry LIKE '%')
GROUP BY s.UserID, Data.fldName, Data.fldSurname
ORDER BY s.UserID

This is the command that works with my data.

SELECT P.Site_Id, MAX(P.LastUpdate), (SELECT TOP 1 Product_Number FROM Product WHERE Site_Id = P.Site_Id ORDER BY LastUpdate DESC) AS Product_Number_2, S.Site_Name
FROM Product P
RIGHT OUTER JOIN Site S ON P.Site_Id = S.Site_Id
WHERE P.LastUpdate BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND CONVERT(DATETIME, '2007-05-05 00:00:00', 102)
AND (S.Site_Name LIKE '%')
GROUP BY P.Site_Id, S.Site_Name
ORDER BY P.Site_Id
0
 
appariCommented:
>> and the User IDs are not grouped.
what do you want exactly?
in your sql your group by is
GROUP BY s.UserID, s.fldDate, s.fldAction,Data.fldCountry, Data.fldName, Data.fldSurname

so its grouping by the other columns also. if you want to group by only userid change the group by clause to
GROUP BY s.UserID

and
for these fields(s.fldDate, s.fldAction,Data.fldCountry, Data.fldName, Data.fldSurname
) use some aggregate function.
0
 
appariCommented:
otherwise post sample data and the sample result you want.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the problem when grouping by datetime field is the time part.

SELECT     s.UserID AS [User ID], convert(varchar, s.fldDate, 101) AS [DATE], s.fldAction AS Action, Data.fldName AS First, Data.fldSurname as Last
FROM         Stats s RIGHT OUTER JOIN
                      Data ON s.UserID = Data.ID
GROUP BY s.UserID, convert(varchar, s.fldDate, 101), convert(varchar(10), s.fldDate, 120) s.fldAction,Data.fldCountry, Data.fldName, Data.fldSurname
HAVING      (s.fldDate BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND CONVERT(DATETIME, '2007-05-05 00:00:00', 102)) AND
                      (Data.fldCountry LIKE '%')
ORDER BY convert(varchar(10), s.fldDate, 120) , Data.fldCountry
0
 
AdiAuthor Commented:
appari -
I have changed my query as you said and removed the other columns from the group by command and now  I get an error:
---------------------------
SQL Server Enterprise Manager
---------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Column 's.fldDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
---------------------------
OK   Help  
---------------------------
This is my changed query:
SELECT     s.UserID AS [User ID], s.fldDate AS DATE, s.fldAction AS Action, Data.fldName, Data.fldSurname
FROM         Stats s RIGHT OUTER JOIN
                      Data ON s.UserID = Data.ID
GROUP BY s.UserID
HAVING      (s.fldDate BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND CONVERT(DATETIME, '2007-05-05 00:00:00', 102)) AND
                      (Data.fldCountry LIKE '%')
ORDER BY s.fldDate, Data.fldCountry

I would like to get a result that looks a bit like this:
 User ID   Date           Action  First     Last
    ---------   -------          --------  -----    -----------
      3385      5/2/2007      3      Dan      Goldman
      3377      5/2/2007      3      Lone      Storie
      3387      5/2/2007      1      James Trausti
      3386      5/2/2007      0      Rogo   Ldwvlien

with only one result per user
THANKS! :)

0
 
appariCommented:
you have to ise aggregate functions on the other fields not in group by.

SELECT     s.UserID AS [User ID], max(s.fldDate) AS DATE, max(s.fldAction) AS Action, max(Data.fldName), max(Data.fldSurname)
FROM         Stats s RIGHT OUTER JOIN
                      Data ON s.UserID = Data.ID
GROUP BY s.UserID
HAVING      (s.fldDate BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND CONVERT(DATETIME, '2007-05-05 00:00:00', 102)) AND
                      (Data.fldCountry LIKE '%')
ORDER BY s.fldDate, Data.fldCountry

0
 
Red_EyeCommented:
As you are grouping by userID the other fields need to either be in the group for example group by UserID s.fldDate etc. or an aggregate function like this

SELECT UserID, MAX(dateCreate) FROM SomeTable
GROUP BY UserID
0
 
AdiAuthor Commented:
Can anyone help me with that?
I want to create a table containing data from both tables in this format


 User ID   Date           Action  First     Last
    ---------   -------          --------  -----    -----------
      3385      5/2/2007      3      Dan      Goldman
      3377      5/2/2007      3      Lone      Storie
      3387      5/2/2007      1      James Trausti
      3386      5/2/2007      0      Rogo   Ldwvlien
Those are my tables
http://i11.tinypic.com/4um6v5y.jpg

The log table can contain the same user ID more then once and I want to show the latest record for each user (using the fldDate on the LOG table).

I'm increasing the points for this one.
Thanks
0
 
AdiAuthor Commented:
sorry, I meant I want to show only one record per user ID.
0
 
cmgarnettCommented:
SELECT DISTINCT s.UserID AS [User ID], s.fldDate AS DATE, s.fldAction AS Action, Data.fldName, Data.fldSurname
FROM Stats s
RIGHT OUTER JOIN Data ON s.UserID = Data.ID
GROUP BY s.UserID
WHERE s.fldDate BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND CONVERT(DATETIME, '2007-05-05 00:00:00', 102)
AND (Data.fldCountry LIKE '%')
ORDER BY s.UserID, s.fldDate, s.fldAction DESC, Data.fldName, Data.fldSurname
0
 
cmgarnettCommented:
Sorry, forgot to remove the GROUP BY

SELECT DISTINCT s.UserID AS [User ID], s.fldDate AS DATE, s.fldAction AS Action, Data.fldName, Data.fldSurname
FROM Stats s
RIGHT OUTER JOIN Data ON s.UserID = Data.ID
WHERE s.fldDate BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND CONVERT(DATETIME, '2007-05-05 00:00:00', 102)
AND (Data.fldCountry LIKE '%')
ORDER BY s.UserID, s.fldDate, s.fldAction DESC, Data.fldName, Data.fldSurname
0
 
AdiAuthor Commented:
cmgarnett - Thanks for your answer but i'm still getting two or more records per ID because the action field in the LOG table is different in each row (that's why the DISTINCT parameter is not working)
this is my result:
       ID           Date         Action    First      Last
      3352      5/3/2007      3      Ann-1      jjjj
      3352      5/3/2007      2      Ann-1      jjjj
      3377      5/2/2007      3      Shane      ste
      3377      5/2/2007      2      Shane      ste
      3383      5/3/2007      3      LUI Alejendro      Bfstamante Dwlon
      3383      5/3/2007      2      LUI Alejendro      Bfstamante Dwlon
      3385      5/1/2007      1      Duch Golding
      3385      5/1/2007      0      Duch Golding
      3385      5/2/2007      3      Duch Golding
      3385      5/2/2007      2      Duch Golding

and I want something a little more like this:
       ID           Date         Action    First      Last
      3352      5/3/2007      2      Ann-1      jjjj
      3377      5/2/2007      2      Shane      ste
      3383      5/3/2007      2      LUI Alejendro      Bfstamante Dwlon
      3385      5/2/2007      2      Duch Golding

any other ideas? :/
0
 
cmgarnettCommented:
I really don't understand how you expect to get only one line for ID 3352 when they have two different Action values, unless the action column is a count of the number of records found for 3352.
0
 
AdiAuthor Commented:
cmgarnett - I want the later in date.. in the new version of my software I've also added time to the date so all I want is the latest entry for each ID
0
 
cmgarnettCommented:
Vista, I managed to get a version running but had to rush off quickly to collect my little boy from nursery. I'll submit it tomorrow morning when I'm back in the office.
0
 
AdiAuthor Commented:
cmgarnett - Thanks I'm awaiting your solution, please don't forget me.
Thanks again!
0
 
AdiAuthor Commented:
Awwww... at last... Thank you cmgarnett!!
:)

This is my final fixed query:
---------------------------------
SELECT s.UserID AS [User ID], MAX(s.fldDate) AS DATE, (SELECT TOP 1 fldAction FROM Stats WHERE UserID = s.UserID ORDER BY fldDate DESC) AS Action, Data.fldName, Data.fldSurname
FROM Stats s
RIGHT OUTER JOIN Data ON s.UserID = Data.ID
WHERE s.fldDate BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND CONVERT(DATETIME, '2007-05-05 00:00:00', 102)
AND (Data.fldCountry LIKE '%')
GROUP BY s.UserID, Data.fldName, Data.fldSurname, s.fldDate
ORDER BY s.UserID

Thanks for the different view-angle I didn't think about using a sub query in this one.
0
 
AdiAuthor Commented:
Sorry, my mistake, I forgot to remove the s.fldDate in the Group By section:

SELECT     s.UserID AS [User ID], MAX(s.fldDate) AS DATE,
                          (SELECT     TOP 1 fldAction
                            FROM          Stats
                            WHERE      UserID = s.UserID
                            ORDER BY fldDate DESC) AS Action, Data.fldName, Data.fldSurname
FROM         Stats s RIGHT OUTER JOIN
                      Data ON s.UserID = Data.ID
WHERE     (s.fldDate BETWEEN CONVERT(DATETIME, '2007-05-01 00:00:00', 102) AND CONVERT(DATETIME, '2007-05-05 00:00:00', 102)) AND
                      (Data.fldCountry LIKE '%')
GROUP BY s.UserID, Data.fldName, Data.fldSurname
ORDER BY s.UserID
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.