Member Avatar for nokomoli

Hi Guys ,

i have faced a sql command problem. That is currently i have 2 different table

a) Select ID, Name from Table1 where ID >=1 and ID ID <=3
  ID      |    Name
  1       |     Alan
  2       |     Amy
  3       |    Andrew
b) Select ID,Sum(Points) as [Total] from Table2 where  
ID >=1 and ID <=3
[No Record From ID > =1 until ID <=3]

My question is how to combine both queries so i can make my table result at below:
ID | Name | Total
1 | Alan | 0
2 | Amy | 0
3 | Andrew | 0

  • 4 Contributors
  • 5 Replies
  • 218 Views
  • 2 Years Discussion Span
  • Latest Post Latest Post by gennesis

Dani AI

Generated

Brief summary: the desired result (every ID from Table1 with a summed Total from Table2, showing 0 when no rows exist) is best done by aggregating Table2 by ID and LEFT JOINing that result to Table1, then converting NULL to 0. As demonstrated, a correlated subquery will work for small datasets, but pre-aggregating + LEFT JOIN is clearer and usually faster at scale.

Example pattern (SQL Server):

SELECT t1.ID, t1.Name, ISNULL(t2.TotalPoints, 0) AS Total
FROM Table1 AS t1
LEFT JOIN (
  SELECT ID, SUM(Points) AS TotalPoints
  FROM Table2
  GROUP BY ID
) AS t2 ON t2.ID = t1.ID
WHERE t1.ID BETWEEN 1 AND 3
ORDER BY t1.ID;

Practical tips and pitfalls:

  • Always pre-aggregate (GROUP BY) before joining when Table2 has multiple rows per ID; joining first and then SUMming can double-count.
  • Use ISNULL (or COALESCE) to turn NULL into 0 for users who expect zeros.
  • Prefer explicit ANSI JOINs (shown above) instead of comma joins — they’re clearer and less error-prone.
  • For large tables, ensure an index on Table2(ID) or consider a persisted/filtered aggregate (indexed view or summary table) if the query is frequent.

Notes on related posts: for , where you need sums by category from the same source, conditional aggregation (SUM(CASE WHEN ... THEN value ELSE 0 END)) collapses both queries into one pass. For , aggregate each child table separately and join those aggregates to the parent row; PIVOT/CROSS APPLY can help when you need columns for each child type.

Recommended Answers

All 5 Replies

Member Avatar for sknake

I think this is what you want. Hard to say without the table structures or an accurate description.

Select ID, Name,
(
  Select Sum(Points)
  From Table2
  Where Table2.ID = Table1.ID
) As Total
From Table1
Where ID >= 1 and ID <= 3
Order By ID
Member Avatar for nokomoli

I think this is what you want. Hard to say without the table structures or an accurate description.

Select ID, Name,
(
  Select Sum(Points)
  From Table2
  Where Table2.ID = Table1.ID
) As Total
From Table1
Where ID >= 1 and ID <= 3
Order By ID

Thanks for your helping. I get your means and i can get what i want. Thank you very much

Member Avatar for sknake

Please mark this thread as solved if you are happy with the solution.

Thanks

Member Avatar for derozza

1.

SELECT pos_cashreceiptdetail.LocationCode,pos_cashreceiptpayment.TerminalCode,pos_cashreceiptpayment.ReceiptDate,
pos_cashreceiptdetail.Description,pos_cashreceiptpayment.ReceiptNo,SUM(TotalCost),SUM(TotalPrice)
FROM pos_cashreceiptdetail,pos_cashreceiptpayment
WHERE pos_cashreceiptdetail.Description LIKE 'Raw%'
AND pos_cashreceiptdetail.LocationCode = pos_cashreceiptpayment.LocationCode
AND pos_cashreceiptdetail.ReceiptDate = pos_cashreceiptpayment.ReceiptDate
AND pos_cashreceiptdetail.ReceiptDate BETWEEN '2010-10-06' AND '2010-10-20'
GROUP BY pos_cashreceiptpayment.ReceiptDate,pos_cashreceiptdetail.ReceiptNo

2.

SELECT pos_cashreceiptdetail.LocationCode,pos_cashreceiptpayment.TerminalCode,pos_cashreceiptpayment.ReceiptDate,
pos_cashreceiptdetail.ShortDescription,pos_cashreceiptpayment.ReceiptNo,SUM(Totalprice)
FROM pos_cashreceiptdetail,pos_cashreceiptpayment
WHERE pos_cashreceiptdetail.ShortDescription LIKE 'Food Stall%'
AND pos_cashreceiptdetail.ReceiptDate = pos_cashreceiptpayment.ReceiptDate
AND pos_cashreceiptdetail.ReceiptNo = pos_cashreceiptpayment.ReceiptNo
AND pos_cashreceiptdetail.ReceiptDate BETWEEN '2010-10-06' AND '2010-10-20'
GROUP BY pos_cashreceiptpayment.ReceiptDate

This my problem. How to combine this query? This query come from one same existing table. Please help me then...

Thanks in advance yeah.. cheers

Edited by derozza because: n/a
Member Avatar for gennesis

Hi a have 4 tables

Table A
ID | Data 1
---------------
1 | A

Table B
ID | Data 2
-----------------
1 | B
1 | C

Table C
ID | Data 3
----------------
1 | D
1 | E
1 | F

Table D
ID | Data 4
----------------
1 | G
1 | H

I want to have this result

Table Result
ID | Data 1 | Data 2 | Data 3 | Data 4
-----------------------------------------------------------------------
1 | A | B | D | G
| | C | E | H
| | | F |

Pls help.....

commented: Don't hijack dead threads -2
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.