i have a table with the columns such id, tid, companyid, ttype etc..
the id may be same for many companyid but unique within the companyid and tid is always unique and
i want to calculate the total no of transactions entered in the table,
a single transaction may be inserted in more than one row,
for example,
id tid companyid ttype
1 1 1 xxx
1 2 1 may be null
2 3 1 yyy
2 4 1 may be null
2 5 1 may be null
the above entries should be counted as only 2 transactions ..
it may be repeated for many companyids..
so how do i calculate the total no of transactions entered in the table
i tried
select sum(count(*)) from transaction group by id,companyId;
but doesn't work
select count(*) from transaction group by id;
wont work becau开发者_StackOverflowse the id may be repeated for different companyids.
Try this
SELECT SUM(s.counts) transactions FROM (SELECT COUNT(*) counts FROM transaction GROUP BY id,companyId) s;
You may need to rephrase your requirement. But from my understanding, following could work:
select count( distinct id, companyid ) from transaction ;
精彩评论