# How to count data in a column and display a text status comment..?

Experts:

I have this in my table:

ORDERID    PAYID
1000              1
1000              2
1000              2
1000              2
1001              1
1001              2
1001              2
1002              2
1003              2
1003              2

The 1's represent APPROVED,....the 2's mean NSF or ....non sufficient funds. We didn't get paid.....

I want to achieve this:

ORDERID     NSFCOUNT
1000                    3
1001                    2
1002                    1
1003                    2

How can I do this...?
Thanks
MikeV
LVL 17
###### Who is Participating?

Commented:
Select t1.ORDERID, COUNT(t1.PAYID) 'NSFCOUNT' FROM urTable t1 WHERE t1.PAYID = 2
AND  t1.DATE > (SELECT MAX(t2.DATE) FROM urTable t2 WHERE t2.PAYID = 1 AND t1.ORDERID = t2.ORDERID GROUP BY t2.ORDERID)
GROUP BY t1.ORDERID
0

Commented:
Select ORDERID, COUNT(PAYID) FROM urTable WHERE PAYID = 2
GROUP BY ORDERID
0

Commented:
Slight correction

Select ORDERID, COUNT(PAYID) 'NSFCOUNT' FROM urTable WHERE PAYID = 2
GROUP BY ORDERID
0

Commented:
try this
select orderid, count(orderid) as payid from sometable where somecriteria group by orderid
0

Commented:
lol should have checked for other answers first
0

Commented:
SELECT ORDERID
,COUNT(*) AS NFSCOUNT
FROM <TableName>
WHERE PayId     = 2
GROUP
BY ORDERID
ORDER
BY ORDERID
0

Senior DBACommented:
It depends -- if you don't need to show a zero count, that is, you want to list them *only* those rows that had at least one NSF, then you can use srafi78's method.
0

Software Solutions ConsultantAuthor Commented:
SORRY.....I "over-simplified" it too much.........see below:

I have this in my table:

ORDERID    PAYID      DATE
1000              1           01/01/07
1000              2           01/02/07
1000              1           01/03/07
1000              2           01/04/07
1000              2           01/05/07
1000              2           01/06/07
1001              1           01/01/07
1001              2           01/02/07
1001              2           01/03/07
1001              1           01/04/07
1001              2           01/05/07
1001              2           01/06/07
1002              2           01/01/07
1003              2           01/01/07
1003              2           01/02/07
1003              1           01/03/07
1003              1           01/04/07
1003              2           01/05/07

The 1's represent APPROVED,....the 2's mean NSF or ....non sufficient funds. We didn't get paid.....

I'm only concerned with the LATEST or MOST RECENT "2's"...I need to be able to COUNT the 2's starting fromt he most RECENT one BACKWARDS....up to the LAST PAYMENT RECIEVED. We will be calling customers based on the Number of NSF's received so I only need to count the most recent series.

I want to achieve this:

ORDERID     NSFCOUNT
1000                    3
1001                    2
1002                    1
1003                    1

Hope it makes sense..the key is that IF they make a payment to us...represented by a '1' then this would reset their NSF COUNT. We are only concerned about their LATEST batch or series of NSF's and being able to COUNT them up.

MikeV

0

Commented:
Select ORDERID, COUNT(PAYID) 'NSFCOUNT' FROM urTable t1 WHERE PAYID = 2
AND  t1.DATE > (SELECT MAX(DATE) FROM urTable WHERE PAYID = 1 AND t1.ORDERID = t2.ORDERID GROUP BY ORDERID)
GROUP BY ORDERID
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.