?
Solved

SQL syntax to count the number of rows where the sum of a column is equal or greater than a value

Posted on 2013-12-13
3
Medium Priority
?
448 Views
Last Modified: 2013-12-13
Hi,

I have a table that records points that users are awarded. A user can have multiple rows in a table as points are awarded at different times. I am trying to write a SQL statement that will count the number of users that have total points greater or equal to 12 points. For example:

User       Points      Date
John        2             2013-12-01
Adam      4             2013-12-02
Mike       6             2013-12-02
John              6             2013-12-04
Mike              8             2013-12-05
John          6             2013-12-06
 
So in this example the count would be 2 (John and Mike).

Any help would be greatly appreciated. Thank you
0
Comment
Question by:bootneck2222
3 Comments
 
LVL 11

Accepted Solution

by:
Louis01 earned 2000 total points
ID: 39716074
select count(*) 
  from (select [User], SUM(points) as sum_points
          from @myTable
         group by [User]
        having SUM(Points) > 12) t1

Open in new window

0
 
LVL 6

Expert Comment

by:Argenti
ID: 39716078
select user, sum(points)
from mytable t
group by user
having sum(points) >= 12

Open in new window

0
 

Author Closing Comment

by:bootneck2222
ID: 39717007
Thank you Louis01.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

592 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