How do I select giving total counts

[Tison]

I want to select companies from a table giving result like this ;
Total of A ; 3
A company name
AB company
AJHG company
Total of B ; 1
BGTH company
Total of C ; 0
Total of D ; 2
....etc....

 

[scriptdan]

Not identical to format you requested, but I believe this gives results you are looking for. I limited results with a where clause so you could see example of output.

[navy]SELECT substring(stateProvincenm,1,1),
       stateProvincenm
FROM StateProvince
WHERE countryCd='US' AND substring(stateProvincenm,1,1) in ('A','C')
ORDER BY substring(stateProvincenm,1,1)
COMPUTE count(substring(stateProvincenm,1,1)) by substring(stateProvincenm,1,1)
[/navy]

Results
stateProvincenm
- -----------------------
A Alaska
A Alabama
A Arizona
A Arkansas

Compute Result:
-----------
4
stateProvincenm
- -----------------------
C Colorado
C California
C Connecticut

Compute Result:
-----------
3