Jump to content
n1concepts

MySQL Aggregate Query with multiple Conditions

Recommended Posts

Posted (edited)

Hi,

I need some help to define the correct SQL query that will accomplish the following:

VERSION: 10.0.38-MariaDB

  • Select the 'member_id' based on the COUNT function using the 'receiver_id' column in the 'members' table
    • Conditions are:
      • WHERE 'astatus = 2' for 'member_id' (this condition 1st to filter before next condition actioned
      • COUNT(receiver_id) is LESS than 4 or IS NULL

The goal is to list the member_id that meets those two conditions.

Now, I have two queries defined which giving me the results I want but I'm trying to combined those two into one query AND add the WHERE condition for 'astatus = 2').

I need help with this part as I can't see the logic to combined the two queries with OR statement and also (for both) add the condition for the 'astatus = 2'.

Any help, surely, appreciated - thx!

Screen shot of table excerpt below along with queries

# Get Count of donors assigned to a receiver_id
SELECT m.receiver_id, COUNT(m.receiver_id) AS donor_count   
FROM members AS m 
WHERE (m.receiver_id IS NOT NULL) 
GROUP BY m.receiver_id 
HAVING COUNT(m.receiver_id) < 4 
LIMIT 1;


# Find member that's not yet assigned a receiver_id
SELECT m.member_id 
FROM members AS m 
WHERE (m.receiver_id IS NULL) 
Limit 4;

 

image1.png

Edited by n1concepts

Share this post


Link to post
Share on other sites

You red text in the screenshot confuses me, but it sounds like you want to find the first member_id that has less than entries in the receiver_id column but that doesn't really make any sense to me.

I think you'll need to explain what your desired end result and goal is better.

Share this post


Link to post
Share on other sites

Hi Kicken,

my apologizes - i can see how that explanation is confusion so let me provide the answer (it came to me right after I posted for help...)

Answer: UNION - I just joined the two queries based on the UNION clause to get the results I needed (worked perfectly after some adjustments to each query).
Yes, you are correct - I was after the 'member_id' which is the primary_key in table which is also 'receiver_id' 2nd key.

thx & problem sovled!

Share this post


Link to post
Share on other sites

For future reference, I would have helped but I had a problem loading that picture of your data into a test table.

Normally I would load posted data into a test table to work on a problem but queries wouldn't work with all the data in a blob field.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.