SQL - Count number of times an ID is in another table without using COUNT()-CodePudding

http://sqlfiddle.com/#!9/74844b/44

I have one table, with each row being a unique ID. I have another table, with aggregate rows that is sorted by each instance the ID appears in the past.

How can I get a count of that table's rows without using COUNT? I can use JOIN or anything else.

ORDERS

Date	USERID
2021-06	3333
2021-09	4444

AGG

Date	User	TransactID
2021-06	3333	1234
2021-03	3333	XXXX
2021-02	3333	XXXX
2021-09	4444	1238
2021-05	4444	XXXX
2021-01	4444	XXXX

RESULT

TXN_ID	USERID	USERID_count
1234	33333	3
1238	44444	3

CodePudding user response：

You can try to use OUTER JOIN with aggregate function instead of subqury.

Query 1:

select max(s.TXN_ID) TXN_ID,a.UserID, count(*) as USERID_count
from AGG a 
LEFT JOIN SDP s ON a.USERID = s.USERID
AND a.DT = s.DT
GROUP BY  a.UserID

Results:

| TXN_ID | UserID | USERID_count |
|--------|--------|--------------|
|   1234 |  33333 |            3 |
|   1238 |  44444 |            3 |

CodePudding user response：

You can either use sum like so

SUM(x>= 0 and y<= 40) AS count_xx

or table rows like so

SELECT TABLE_ROWS from INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME = 'foo'

or even found_rows

SELECT SQL_CALC_FOUND_ROWS * FROM fooWHERE bar="value" LIMIT 10;SELECT FOUND_ROWS();

CodePudding user response：

Well I think you already have that info in column count_rank :

select SDP.USERID, max(Count_rank) 1
from SDP left join AGG on AGG.UserID = SDP.USERID
group by SDP.USERID

DEMO