CODEWITHSUNDEEP
sql-serversql-server-2008
Vishal Suthar
Vishal Suthar

Reputation: 17194

Join Query for fetching the Count

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

Answers (5)

Sai Kalyan Kumar Akshinthala
Sai Kalyan Kumar Akshinthala

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.

enter image description here

Upvotes: 1

Vikram
Vikram

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

Java
Java

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

Ghost Answer
Ghost Answer

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

chinna_82
chinna_82

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

Related Questions