Reputation: 17194
tbl_TransactionDishout
Trnx_id offerNo TerminalID Created VirtualCard
-------------------------------------------------------------------
1 1014 170924690436418 2010-05-25 12:51:59.547 1234567890
2 1014 123924690436418 2010-05-23 12:51:59.547 1234567890
3 1015 144524690436418 2010-05-23 12:51:59.547 1234567890
tbl_Offer
OFID Offer_Text
------------------
1014 Test1
1015 Test2
Now I want the Output to be like this:
OFID Offer_Text Counter
------------------------------
1014 Test1 2
1015 Test2 1
Is it possible with the single query..?
Upvotes: 0
Views: 243
Reputation: 11844
You can write like this
SELECT O.OFID, O.Offer_Text, COUNT(t.offerNo) AS Counter FROM tbl_Offer AS O
INNER JOIN tbl_TransactionDishout AS t ON O.OFID = t.offerNo GROUP BY O.OFID, O.Offer_Text
EDIT :
The following is the output screen shot when I tried it in my system.
Upvotes: 1
Reputation: 8333
try this:
select t2.OFID, cast(t2.Offer_Text as varchar(max)), Count(*)
from tbl_TransactionDishout t
inner join tbl_Offer t2 on cast(t.offerNo as varchar(max)) = cast(t2. OFID as varchar(max))
group by t2.OFID, cast(t2.Offer_Text as varchar(max))
Upvotes: 1
Reputation: 2489
Try this one , it will work
select t1.OFID,t1.Offer_text, count(t2.offerNo)
from tbl_Offer t1 inner join tbl_TransactionDishout t2
on t1.OFID= t2.offerNo
group by t1.OFID,t1.Offer_text
Upvotes: 0
Reputation: 1498
try this query
select OFID, Offer_Text, Trnx_id as Counter from tbl_offer left join tbl_TransactionDishout on transactionDishout.offerNo = tbl_Offer.OfferText ordered by Trnx_id desc
Upvotes: 0
Reputation: 6403
select a.offerNo,Offer_Text,COUNT(a.TerminalID) as Counter
from
tbl_TransactionDishout a,
tbl_Offer b
where a.OFID = b.OFID
group by OFID,Offer_Text;
Upvotes: 0