By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,648 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Getting the count from two tables as rows not columns.

P: 9
I am building a site that uses a redemption card system and thus need to display some redemption data in a chart on an ASPX page. The chart needs the data in the following format.

Status | Count
-------------------------------------------------
NotReemed | 25000
Redeemed | 14000

I am using currently using the following SQL Statement to obtain the data

---
SELECT
NotRedeemed =(SELECT count(*)
FROM tbl_codes INNER JOIN
tbl_batches ON tbl_codes.Batch_ID = tbl_batches.Batch_ID
WHERE (tbl_codes.IsUsed = 0 AND LEFT(tbl_codes.Redeem_Code_Prefix = 'WHATEVER')

Redeemed =(SELECT count(*)
FROM tbl_fans
WHERE LEFT(Redeem_Code_Prefix = 'WHATEVER'
---
Using this obtains the correct data but the data is obviously in the following format:

NotRedeemed | Redeemed
------------------------------------------------
25000 | 14000

If someone could give me an idea of how to get the data in the correct format (as per the first example) it would be much appriciated.
Apr 1 '09 #1
Share this Question
Share on Google+
2 Replies


100+
P: 152
gpl
Try something like this

Expand|Select|Wrap|Line Numbers
  1. Select 'NotRedeemed' As [Status],
  2.        (Select Count(*)
  3.         From   Tbl_codes
  4.                Inner Join Tbl_batches
  5.                  On Tbl_codes.Batch_id = Tbl_batches.Batch_id
  6.         Where  Tbl_codes.Isused = 0
  7.                And Left(Tbl_codes.Redeem_code_prefix, 8) = 'WHATEVER') As [count]
  8. Union All
  9. Select 'Redeemed' As [Status],
  10.        (Select Count(*)
  11.         From   Tbl_fans
  12.         Where  Left(Redeem_code_prefix, 8) = 'WHATEVER') As [count]
  13.  
  14.  
Apr 1 '09 #2

P: 9
Thanks heaps, that is exactly what I was looking for:)
Apr 1 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.