Group By - select by a criteria that is met every month

group by sql
mysql every
sql having
sql aggregate functions
sql case
mysql aggregate functions
group by mysql
conditional running total in sql

The below query returns all USERS that have SUM(AMOUNT) > 10 in a given month. It includes Users in a month even if they don't meet the criteria in other months.

But I'd like to transform this query to return all USERS who must meet the criteria SUM(AMOUNT) > 10 every single month (i.e., from the first month in the table to the last one) across the entire data.

Put another way, exclude users who don't meet SUM(AMOUNT) > 10 every single month.

select USERS, to_char(transaction_date, 'YYYY-MM') as month
from Table
GROUP BY USERS, month
HAVING SUM(AMOUNT) > 10;

One approach uses a generated calendar table representing all months in your data set. We can left join this calendar table to your current query, and then aggregate over all months by user:

WITH months AS (
    SELECT DISTINCT TO_CHAR(transaction_date, 'YYYY-MM') AS month
    FROM yourTable
),
cte AS (
    SELECT USERS, TO_CHAR(transaction_date, 'YYYY-MM') AS month
    FROM yourTable
    GROUP BY USERS, month
    HAVING SUM(AMOUNT) > 10
)

SELECT
    t.USERS
FROM months m
LEFT JOIN cte t
    ON m.month = t.month
GROUP BY
    t.USERS
HAVING
    COUNT(t.USERS) = (SELECT COUNT(*) FROM months);

The HAVING clause above asserts that the number of months to which a user matches is in fact the total number of months. This would imply that the user meets the sum criteria for every month.

National Institute on Drug Abuse Statistical Series: Series G., Trends in drug mentions in six-month intervals are shown over a three-year period for The panel selected for this report includes facilities which reported at least once For facilities which met these criteria, data for missed reporting days during For each drug group within each metropolitan area and the total DAWN​  To sum data by month, ignoring year, you can use a formula based on the SUMPRODUCT and MONTH functions. In the example shown, the formula in H6 is: = SUMPRODUCT (( MONTH ( dates ) = 3 ) * amounts ) The result is a total of all sales in March,

Perhaps you could use a correlated subquery, such as:

select t.*
from (select distinct table.users from table) t
where not exists 
(
    select to_char(u.transaction_date, 'YYYY-MM') as month 
    from table u 
    where u.users = t.users 
    group by month
    having sum(u.amount) <= 10
)

Using the aggregate functions ANY, SOME, EVERY with MySQL , The first column is the number of the month, between 1 and 12, the second column select month, sum(amount) from sales group by month; workaround, they do have a little catch, which fortunately you are unlikely to meet. GROUP BY cannot be used alone because it only returns 1 row per group (category). You can use a sub query with flag = 1 and INNER JOIN : SELECT d1.ID, d1.category, d1.flag FROM data d1 INNER JOIN ( SELECT DISTINCT category FROM data WHERE flag = 1 ) d2 ON d2.category = d1.category ;

One option would be using sign(amount-10) vs. sign(amount) logic as

SELECT q.users
  FROM
    (
    with tab(users, transaction_date,amount) as
    (
     select 1,date'2018-11-24',8 union all
     select 1,date'2018-11-24',18 union all    
     select 2,date'2018-10-24',13 union all
     select 3,date'2018-11-24',18 union all    
     select 3,date'2018-10-24',28 union all
     select 3,date'2018-09-24', 3 union all    
     select 4,date'2018-10-24',28
    )
    SELECT users, to_char(transaction_date, 'YYYY-MM') as month, 
           sum(sign(amount-10)) as cnt1,
           sum(sign(amount)) as cnt2
      FROM tab t
     GROUP BY users, month
    ) q
  GROUP BY q.users  
  HAVING sum(q.cnt1) = sum(q.cnt2)
  GROUP BY q.users

  users
  -----
    2
    4

Rextester Demo

Management Fundamentals: Concepts, Applications, and Skill Development, Generally, with simple programmed decisions, the objectives and the criteria have been set. Therefore, the second step for the individual or group facing such a decision requires Criteria are the standards an alternative must meet to be selected as the decision The objective is to hire a store manager by next month. Here we've added an additional criteria, the named range "priorities" paired with H4 for the criteria itself. In this version of the formula, we get a count by month broken down by the priority, which is picked up directly from the header in row 5. This formula uses both mixed references and absolute references to facilitate copying:

You need to compare the number of months > 10 to the number of months between the min and the max date:

SELECT users, Count(flag) AS months, Min(mth), Max(mth)
FROM
 (
   SELECT users, date_trunc('month',transaction_date) AS mth, 
      CASE WHEN Sum(amount) > 10 THEN 1 end AS flag
   FROM tab t
   GROUP BY users, mth
 ) AS dt
GROUP BY users  
HAVING -- adding the number of months > 10 to the min date and compare to max
   Min(mth) + (INTERVAL '1' MONTH * (Count(flag)-1)) = Max(mth)

If missing months don't count it would be a simple count(flag) = count(*)

What’s New in Cardiovascular Imaging?, If the criteria were not met, upsizing of the PTCA balloon was performed by selecting a The operator was required to select the shortest stent necessary to cover the of the total cohort) but type C lesions were more common in the group requiring At 5 months clinical follow-up, the cumulative incidence of major cardiac  The Criteria is cell D12 (East). An Average_range of cells E3 to E9, which contains the average sales by each employee. So if data in the range C3:C12 equals East , then the total sales for that record are averaged by the function.

Mentoring in Nursing and Healthcare: Supporting Career and , To ensure the selection of potential mentee participants was a fair and just from each of the six NHS trusts involved who they felt met the selection criteria and select those to participate in the programme and those to form a control group. decided not to continue to participate within the first months of the programme. Start your formula by selecting the data range (without names and months) and multiply it by the array containing months that are equal to 6. Array1 of the formula instructs Excel to add up all the numbers in the selection C9:N14. Array2 checks for each month number whether it is equal to 6.

Federal Register, The NCUA Board believes that students are a unique group that can be considered students have arisen in the last eighteen months that require clarification. the association will be considered to have met the voting requirement criterion. as well as the addition of select groups to a multiple common bond credit union,  Today let’s look at how to copy cells based on specific criteria in your Excel worksheet. This is a macro I created to help speed up a repetitive process at work. I needed to copy and paste cells that were greater than zero. Specifically, all products that had a value greater than zero next to them.

Use Queries to show records based on conditions – Documentation , where with criteria, Board Games, A collection of 6 board games Explanation: Select all pertinent columns that we want to show, then Explanation: Select all columns where data in column D (deadlines) is the same as that of the current month and year Hello, is it possible to use a group by statement? One row is returned for each group, so aggregate functions in the SELECT list will provide information about each group rather than individual rows: SELECT MONTHNAME(CREATION_DATE) AS Month, COUNT(*) AS CASES FROM TA_CASES WHERE MONTH(CREATION_DATE) = 4 AND Year(CREATION_DATE) = 1998 GROUP BY MONTH(CREATION_DATE);

Comments
  • What does "every single month" mean? Every month the user has an entry? Every month some user has an entry? Every month from the first month in the table to the last one? Every month in a certain time span, e.g. Jan 2018 to Oct 2018?
  • It is correct with respect to each month as you specified group by users,month meaning every single month group but the fact you have month with year in it doesnt mean for other years it will be same so if you can change your month format to Just 'MM'
  • @Thorsten Kettner I only want to return users who have SUM(AMOUNT) > 10 from the first month in the table to the last one i.e., all months.
  • What if there's a missing month for a user?
  • @dnoeth then they should be excluded - I'm essentially trying to build a database of consistent users
  • this returns dates for the min, max values (i.e., 2015-04-01 for min), not the number/count of months - is this intended? It's not returning the correct value as is.
  • Well, Count(flag) AS months calculates the number of months. This query returns those users who had amounts > 10 from the min to the max month and no gaps. If this is not what you want you should explain why and setup a fiddle.