Jump to content

MySQL Aggregate Query with multiple Conditions

Recommended Posts


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 

# 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;



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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

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.