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
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
Last Comment
ASKER
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
Not quite sure how the above comment answers the questions in my first comment.
Please elaborate.
Please elaborate.
ASKER
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
That should work - Thanks
Thanks for the grade. Good luck with your project. -Jim
Databases
Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.
62K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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.