Jump to content

MySQL Aggregate Query with multiple Conditions


n1concepts
 Share

Recommended Posts

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
Link to comment
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.

Link to comment
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!

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

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

Guest
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.

 Share

×
×
  • 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.