Member Avatar for RC_820

hii how can i make self join and sum data in other table by username
i have table like this 

members (id, username, referral)
ref_bonus (id, username, bonus)

members
id  | username | referral
----------------------
1    id1        id2
2    id2        null
3    id3        id2

ref_bonus
id | username | bonus
----------------------
11    id2      1.00
21    id2      1.00
31    id2      1.00

the result i want is select all member have referral and sum bonus in ref_bonus
example result is like this : 

id  | username | count_ref | sum_bonus
----------------------
1    d2          2         | 3.0
Edited by RC_820
  • 2 Contributors
  • 1 Reply
  • 134 Views
  • 3 Hours Discussion Span
  • Latest Post Latest Post by pritaeas

Dani AI

Generated

A simple, robust pattern is to aggregate referrals and bonuses separately, then join those aggregates back to the member row that represents the referrer. That keeps the intent clear: one aggregation counts how many rows point at a given referrer, the other sums bonuses per username. is on the right track using derived tables and a LEFT JOIN so referrers with no recorded bonus still appear.

If the referrer id is required (not just username), join the referral-aggregation to the members table so the referrer row supplies the id and canonical username. Use an INNER JOIN from the members table to the referral-aggregate (to limit results to members who actually have referrals) and a LEFT JOIN to the bonus-aggregate (so missing bonuses become NULL and can be turned into 0 with COALESCE).

Example pattern (adjust names/types for the actual schema):

SELECT ref.id AS ref_id,
       ref.username AS ref_username,
       COALESCE(rc.count_ref,0) AS count_ref,
       COALESCE(rb.sum_bonus,0) AS sum_bonus
FROM members AS ref
INNER JOIN (
  SELECT referral AS ref_username, COUNT(*) AS count_ref
  FROM members
  WHERE referral IS NOT NULL
  GROUP BY referral
) AS rc ON ref.username = rc.ref_username
LEFT JOIN (
  SELECT username, SUM(bonus) AS sum_bonus
  FROM ref_bonus
  GROUP BY username
) AS rb ON ref.username = rb.username;

Notes and cautions: newer MySQL modes require nonaggregated columns to appear in GROUP BY — avoid relying on nonstandard behavior by selecting ids from the canonical members row rather than pulling nonaggregated columns out of a grouped subquery. Index members.referral and ref_bonus.username for much better join performance. For long-term data integrity, prefer storing a referrer id (integer) instead of a username string to avoid problems if usernames change. For JOIN/GROUP BY details see MySQL documentation: JOINs and .

Member Avatar for pritaeas

Something like this perhaps? (untested)

SELECT A.username, A.count_ref, B.sum_bonus FROM
(SELECT referral AS username, COUNT(1) AS count_ref FROM members WHERE referral IS NOT NULL GROUP BY referral) AS A
LEFT JOIN (SELECT username, SUM(bonus) AS sum_bonus FROM ref_bonus GROUP BY username) AS B ON A.username = B.username
commented: thank you so much it's work +0
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.