adding count to my existing select query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

adding count to my existing select query

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2003-08-18 : 13:54:10

How do I rewrite this query so that I include a count of all records with blnL3 = 1?

SELECT lngIndex, dteCycleDate, dteTransacDate, txtMerchantName, curTransacAmnt, txtMemoFlag, blnSent, curOrigAmnt, txtAccountNum, curForeignAmnt, txtCompanyNum, blnL3,
--count(blnL3)where blnL3 = 1
FROM Transactions
WHERE (txtAccountNum = @Account) AND ((dteCycleDate = @CycleDateStart) or (dteCycleDate = @Fixed) or (dteCycleDate is NULL))
ORDER BY dteTransacDate,lngParent, lngIndex

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-08-18 : 13:59:15

Why is it undesirable to show the count(*) for all blnL3?

Jonathan
{0}

PeterG
Posting Yak Master

156 Posts

Posted - 2003-08-18 : 14:07:32

This is what I'm trying to do in my asp page.
i.e., if count (of records with blnL3=1) >= 1 then
show columnA
else 'count = 0
don't show columnA

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-08-18 : 14:35:17

Well, you can't optionally return a column or not in a single select; you might try an IF statement that conditionally ran the query, with and without the column in question.

Jonathan
{0}

PeterG
Posting Yak Master

156 Posts

Posted - 2003-08-18 : 15:14:01

No, no, no... you misunderstood me.

From the select query, I need to do a count(blnL3 = 1) and based on this value I will display or not a table column in my asp page.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-18 : 16:12:24

someth like

SELECT lngIndex, dteCycleDate, dteTransacDate, txtMerchantName, curTransacAmnt, txtMemoFlag, blnSent, curOrigAmnt, txtAccountNum, curForeignAmnt, txtCompanyNum, blnL3,
cnt = (select count(*) from Transactions where (txtAccountNum = @Account) AND ((dteCycleDate = @CycleDateStart) or (dteCycleDate = @Fixed) or (dteCycleDate is NULL)) and blnL3 = 1
FROM Transactions
WHERE (txtAccountNum = @Account) AND ((dteCycleDate = @CycleDateStart) or (dteCycleDate = @Fixed) or (dteCycleDate is NULL))
ORDER BY dteTransacDate,lngParent, lngIndex

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources