A query can be wrapped in another SELECT:

SELECT COUNT(*)
FROM
(
SELECT COUNT(SID) tot  -- add alias
FROM Test 
WHERE DATE = '2012-12-10' 
GROUP BY SID
) 
 
SELECT COUNT(*)
FROM
(SELECT COLUMNS 
 FROM SCHEMA.TABLE 
 WHERE nvl(TRADING_BRANCH||TRADING_ACCOUNT, 'ZZZZ') = 'ZZZZ' AND LEGACY_PARTNER_TYPE IN ('DELIVERY', 'MND'))
 
 
SELECT column_name, COUNT(column_name) 
FROM TABLE
GROUP BY column_name
HAVING COUNT (column_name) > 1;