Avatar of JohnMac328
JohnMac328Flag for United States of America asked on

SQL Grouping records problem

I am trying to group records based on the id - it works fine until I need additional filters such as date.  How can I group the records and also display other information.  I included a screenshot to show how it is not displaying correctly - for example I would like the name Marketing to only display once.

Any help is appreciated



SELECT     leadervotes.voteid, SUM(DISTINCT leadervotes.votelevel) AS Expr1, leadervotes.votedate, nominations.astname
FROM         leadervotes INNER JOIN
                      nominations ON leadervotes.voteid = nominations.recordid
GROUP BY leadervotes.voteid, leadervotes.votedate, nominations.astname
HAVING      (leadervotes.votedate > CONVERT(DATETIME, '2013-07-31 00:00:00', 102))
ORDER BY leadervotes.voteid DESC

example
DatabasesMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Jim Horn

8/22/2022 - Mon
Jim Horn

>How can I group the records and also display other information.
Essentially by using subqueries.  Looking at 2137 above, I see two values for votedate, so you'll have to spell out the logic behind which one you want to display.

An excellent idea would be for you to mock up how you wish the return set to look, given the source data in the image.

>for example I would like the name Marketing to only display once.
If your requirements end up dictating that you would have two 2137 rows with the value 'Marketing', better to leave the values in the set, and let SSRS or whatever report tool you are using suppress duplicate values.
ASKER
JohnMac328

Since the filter is for one month the number of records would only be 20 or 30 even though the record number is high for example last month the record was 2100
Jim Horn

Not quite sure how the above comment answers the questions in my first comment.
Please elaborate.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
JohnMac328

The mockup is in the original photo - For example - I want 2137 to appear once and marketing to appear once and the values summed up in the Expr1 field
ASKER CERTIFIED SOLUTION
Jim Horn

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
JohnMac328

That should work - Thanks
Jim Horn

Thanks for the grade.  Good luck with your project.  -Jim
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.