count()

i want to count and display the number of order numbers associated with each state. some states do not have any orders and therefore are null, and i need to display every state. so when i do a select count(order_num), state_code from ...... the states that have no orders are null and not displayed. can i solve this matter in my select statement, or will i have to do it in a procedure?

Comments

  • : i want to count and display the number of order numbers associated with each state. some states do not have any orders and therefore are null, and i need to display every state. so when i do a select count(order_num), state_code from ...... the states that have no orders are null and not displayed. can i solve this matter in my select statement, or will i have to do it in a procedure?
    :
    Hi,
    It depends where you are running this SQL Statement. If you are doing it in a VB Code module then you will have to test for Null on the return value and replace it with zero.
    If, however, you are running this in a SQL-Server Stored Procedure then you can use "IsNull" in the Select statement - SELECT @Count = IsNull( (count.......) , 0)
    Whatever you place in the second part after the comma is returned if the firstpart is null. In your case you want it to be Zero.
    :-)

    [blue][b]In a World full of blind men the one-eyed man is King[/b][blue];-)

  • : : i want to count and display the number of order numbers associated with each state. some states do not have any orders and therefore are null, and i need to display every state. so when i do a select count(order_num), state_code from ...... the states that have no orders are null and not displayed. can i solve this matter in my select statement, or will i have to do it in a procedure?
    : :
    : Hi,
    : It depends where you are running this SQL Statement. If you are doing it in a VB Code module then you will have to test for Null on the return value and replace it with zero.
    : If, however, you are running this in a SQL-Server Stored Procedure then you can use "IsNull" in the Select statement - SELECT @Count = IsNull( (count.......) , 0)
    : Whatever you place in the second part after the comma is returned if the firstpart is null. In your case you want it to be Zero.
    : :-)
    : im using this in powerbuilder and its accessing a sql server db
    : [blue][b]In a World full of blind men the one-eyed man is King[/b][blue];-)
    :
    :

  • : : : i want to count and display the number of order numbers associated with each state. some states do not have any orders and therefore are null, and i need to display every state. so when i do a select count(order_num), state_code from ...... the states that have no orders are null and not displayed. can i solve this matter in my select statement, or will i have to do it in a procedure?
    : : :
    : : Hi,
    : : It depends where you are running this SQL Statement. If you are doing it in a VB Code module then you will have to test for Null on the return value and replace it with zero.
    : : If, however, you are running this in a SQL-Server Stored Procedure then you can use "IsNull" in the Select statement - SELECT @Count = IsNull( (count.......) , 0)
    : : Whatever you place in the second part after the comma is returned if the firstpart is null. In your case you want it to be Zero.
    : : :-)
    : : im using this in powerbuilder and its accessing a sql server db
    : :
    : :
    Have not use powerbuilder so I'm not sure what your return is, presumably you feed the return to a variable, so you just need to put a line of code in that says :
    If IsNull(varReturn) then
    varReturn = 0
    End If
    However, if you are running it as a whole batch and retrieving a Recordset then presumably you are missing entries for those with no count, so you could either get a list of state-code then loop through one at a time so that you can make the above test, or construct the SQL String with the IsNull in it and then pass that to sp_executesql to return a full recordset.



    [blue][b]In a World full of blind men the one-eyed man is King[/b][blue];-)

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories