?
Solved

GROUP BY Not Working

Posted on 2007-08-08
18
Medium Priority
?
247 Views
Last Modified: 2013-11-05
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.
0
Comment
Question by:VistaSystem
  • 8
  • 5
  • 3
  • +2
18 Comments
 
LVL 39

Expert Comment

by:appari
ID: 19654413
>> 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
 
LVL 39

Expert Comment

by:appari
ID: 19654418
otherwise post sample data and the sample result you want.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19654433
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:VistaSystem
ID: 19654547
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
 
LVL 39

Expert Comment

by:appari
ID: 19654589
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
 

Expert Comment

by:Red_Eye
ID: 19654665
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
 

Author Comment

by:VistaSystem
ID: 19654883
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
 

Author Comment

by:VistaSystem
ID: 19654910
sorry, I meant I want to show only one record per user ID.
0
 
LVL 3

Expert Comment

by:cmgarnett
ID: 19654926
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
 
LVL 3

Expert Comment

by:cmgarnett
ID: 19654942
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
 

Author Comment

by:VistaSystem
ID: 19655065
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
 
LVL 3

Expert Comment

by:cmgarnett
ID: 19655144
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
 

Author Comment

by:VistaSystem
ID: 19655196
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
 
LVL 3

Expert Comment

by:cmgarnett
ID: 19658410
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
 

Author Comment

by:VistaSystem
ID: 19660993
cmgarnett - Thanks I'm awaiting your solution, please don't forget me.
Thanks again!
0
 
LVL 3

Accepted Solution

by:
cmgarnett earned 1600 total points
ID: 19661133
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
 

Author Comment

by:VistaSystem
ID: 19661267
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
 

Author Comment

by:VistaSystem
ID: 19661307
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question