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;

    SQL
    1. SELECT count (distinct loc.externalname) "2B"
    2. from ant.wmsstockitem si
    3. join ant.wmsloadunit lu on lu.id = si.loadunit_id
    4. join ant.wmslocation loc on loc.id = lu.location_id
    5. 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;


    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;