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
    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;


    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


    Are there a set amount of externalnames so do you just want 2B and 2A or could there be any number i.e are there 2C 3D etc?

  • 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;

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!