Hi, I have a query below which returns the SUM of AmountSpent field grouped by the respective Membership Card, how can return only records that having AmountSpent of more then the value 100. Thanks.
SELECT SUM(AmountSpent) AmtSpt from
Transact t
INNER JOIN Card c
on t.CardNo = c.CardNo
INNER JOIN Member m
on c.MemberID = m.ID
WHERE m.DeletedBy is null and c.DeletedBy is null and t.VoidBy is null
SELECT t.cardNo, SUM(AmountSpent) AmtSpt
FROM Transact t
INNER JOIN Card c on t.CardNo = c.CardNo
INNER JOIN Member m on c.MemberID = m.ID
WHERE m.DeletedBy is null and c.DeletedBy is null and t.VoidBy is null
GROUP BY t.cardNo
HAVING SUM(AmountSpent)>100
SELECT SUM(AmountSpent) AmtSpt from
Transact t
INNER JOIN Card c
on t.CardNo = c.CardNo
INNER JOIN Member m
on c.MemberID = m.ID
WHERE m.DeletedBy is null and c.DeletedBy is null and t.VoidBy is null
GROUP BY t.cardNo
HAVING SUM(AmountSpent)>100
Thanks,
Kalaivendan
Please Mark as Answer if this post helps you!
Marked as answer by k80sg on Mar 01, 2012 05:41 AM
k80sg
Member
310 Points
340 Posts
SQL + SUM of fields more then a specific value
Mar 01, 2012 02:17 AM|LINK
Hi, I have a query below which returns the SUM of AmountSpent field grouped by the respective Membership Card, how can return only records that having AmountSpent of more then the value 100. Thanks.
SELECT SUM(AmountSpent) AmtSpt from
Transact t
INNER JOIN Card c
on t.CardNo = c.CardNo
INNER JOIN Member m
on c.MemberID = m.ID
WHERE m.DeletedBy is null and c.DeletedBy is null and t.VoidBy is null
GROUP BY t.cardNo
Shellymn
Contributor
2602 Points
485 Posts
Re: SQL + SUM of fields more then a specific value
Mar 01, 2012 02:53 AM|LINK
append the below clause also.
GROUP BY t.cardNo
D J
Contributor
5362 Points
941 Posts
Re: SQL + SUM of fields more then a specific value
Mar 01, 2012 03:26 AM|LINK
sandeepmitta...
Contributor
6779 Points
1058 Posts
Re: SQL + SUM of fields more then a specific value
Mar 01, 2012 04:01 AM|LINK
Sandeep Mittal | My Blog - IT Developer Zone
vendan
Participant
856 Points
293 Posts
Re: SQL + SUM of fields more then a specific value
Mar 01, 2012 04:04 AM|LINK
Hi,
Try Like this
Kalaivendan
Please Mark as Answer if this post helps you!