SQL multiple counts from same column

Hello everyone,
I am just getting into SQL and I am having a rough time figuring this one out. I am trying to pull a distinct count of a column based on the first two charecters. here is what I have so far;

SELECT count (distinct loc.externalname) "2B"
from ant.wmsstockitem si
join ant.wmsloadunit lu on lu.id = si.loadunit_id
join ant.wmslocation loc on loc.id = lu.location_id
where (si.ss_state) = 'WIP_DCC' and loc.externalname LIKE '2B%' AND (si.ss_reasontext) !='Missing best before date'

This returns one row and one column with a count of every unique location that starts with 2B. I am looking for a column that counts every unique location that starts with 1A, then a seperate column for 1B, 2A, 2B, 3A, 3B and so on. Thank you in advance for any help.

Re: SQL multiple counts from same column

Hi all,
I have made a bit of progress. using UNION I can pull the counts I want but they all are under one column with the label of the first query. Here is what I have now;

SELECT count (distinct loc.externalname) "2B" 
from ant.wmsstockitem si 
join ant.wmsloadunit lu on lu.id = si.loadunit_id 
join ant.wmslocation loc on loc.id = lu.location_id 
where (si.ss_state) = 'WIP_DCC' and loc.externalname LIKE '2B%' AND (si.ss_reasontext) !='Missing best before date'
Union
Select count (distinct loc.externalname) "2A"
from ant.wmsstockitem si
join ant.wmsloadunit lu on lu.id = si.loadunit_id
join ant.wmslocation loc on loc.id = lu.location_id
where (si.ss_state) = 'WIP_DCC' and loc.externalname LIKE '2A%' AND (si.ss_reasontext) !='Missing best before date'

I get counts I can use but instead of 1 column for each count with the column labeled, I get 1 column labeled 2B that has a seperate row for every count. While I can make do with it the way it is, I would love to have it display in seperate columns.

Re: SQL multiple counts from same column

There are a set amount. The possible external locations will start with; 1A, 1B, 2A, 2B, 3A, 3B, 4A, 4B, C, D, E, F0, X, Y. For the time being those are the only possibilities.

Re: SQL multiple counts from same column

I have something that will work nicely for me;

Select 
(SELECT count (distinct loc.externalname)
from ant.wmsstockitem si
join ant.wmsloadunit lu on lu.id = si.loadunit_id
join ant.wmslocation loc on loc.id = lu.location_id 
where (si.ss_state) = 'WIP_DCC' and loc.externalname LIKE '2B%' AND (si.ss_reasontext) != 'Missing best before date') as "2B",
(select count (distinct loc.externalname)
from ant.wmsstockitem si
join ant.wmsloadunit lu on lu.id = si.loadunit_id
join ant.wmslocation loc on loc.id = lu.location_id 
where (si.ss_state) = 'WIP_DCC' and loc.externalname LIKE '2A%' AND (si.ss_reasontext) != 'Missing best before date') as "2A"
from
  dual;