Need Assistance -Query (1 Viewer)

Fandy

Member
Local time
Today, 09:05
Joined
Mar 26, 2021
Messages
37
Dsum function.JPG

The above is a sample query I have generated from my table. I was to sum up the total Bill_Amt based on the Client_ID and do the same for
Paid_ Amt based the Client_ID. I tried using the dsum function but not giving me what I'm looking for. My Dsum query gave me sum of Bill_Amt
of all clients and not by each client. I need your assistance please.
 

cheekybuddha

AWF VIP
Local time
Today, 09:05
Joined
Jul 21, 2014
Messages
2,237
You haven't really given us much detail to work with, but you might start with a base query like:
Code:
SELECT
  Client_ID,
  SUM(Bill_Amt) AS Total_Billed,
  SUM(Paid_Amt) AS Total_Paid
FROM Transaction
GROUP BY Client_ID
;
 

Ranman256

Well-known member
Local time
Today, 05:05
Joined
Apr 9, 2015
Messages
4,337
your example shows you are summing for EVERY field in the table.
Sum for ONLY the fields you want, like CheekyBuddha shows.
or
sum per person per date
 

Fandy

Member
Local time
Today, 09:05
Joined
Mar 26, 2021
Messages
37
You haven't really given us much detail to work with, but you might start with a base query like:
Code:
SELECT
  Client_ID,
  SUM(Bill_Amt) AS Total_Billed,
  SUM(Paid_Amt) AS Total_Paid
FROM Transaction
GROUP BY Client_ID
;
Pls hw do I get the balance from your above query. Can you me pls
 

cheekybuddha

AWF VIP
Local time
Today, 09:05
Joined
Jul 21, 2014
Messages
2,237
Try:
Code:
SELECT
  Client_ID,
  SUM(Bill_Amt) AS Total_Billed,
  SUM(Paid_Amt) AS Total_Paid,
  SUM(Bill_Amt) - SUM(Paid_Amt) AS Balance
FROM Transaction
GROUP BY Client_ID
;
 

Fandy

Member
Local time
Today, 09:05
Joined
Mar 26, 2021
Messages
37
Try:
Code:
SELECT
  Client_ID,
  SUM(Bill_Amt) AS Total_Billed,
  SUM(Paid_Amt) AS Total_Paid,
  SUM(Bill_Amt) - SUM(Paid_Amt) AS Balance
FROM Transaction
GROUP BY Client_ID
;
Thank you.
 

Users who are viewing this thread

Top Bottom