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
JohnMac328Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
JohnMac328Author Commented:
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
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Not quite sure how the above comment answers the questions in my first comment.
Please elaborate.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

JohnMac328Author Commented:
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
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>For example - I want 2137 to appear once and marketing to appear once and the values summed up in the Expr1 field
Okay, but you'll have to either lose the votedate column, as it's the reason that there is two rows, or tell us which value should live (2013-08-10 vs. 2013-08-11) and why.

Couple of things:
*  I removed all references to votedate
*  HAVING is when you want to do a WHERE clause on an aggregate such as Sum, Count, etc., so if you're comparing against a single value, not an aggregate, than that should go in the WHERE clause.
*  I used aliases lv and nom to make the code a lot easier to read.
SELECT lv.voteid, SUM(DISTINCT lv.votelevel) AS votelevel_count, nom.lastname
FROM leadervotes lv
	JOIN nominations nom ON lv.voteid = nom.recordid
WHERE lv.votedate > '2013-07-31 00:00:00'
GROUP BY lv.voteid, nom.lastname
ORDER BY lv.voteid DESC

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnMac328Author Commented:
That should work - Thanks
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.