Announcement

Collapse
No announcement yet.

SQL multiple counts from same column

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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;
    Code:
    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.

  • #2
    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;
    Code:
    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.

    Comment


    • #3
      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?

      Comment


      • #4
        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.

        Comment


        • #5


          Re: SQL multiple counts from same column

          I have something that will work nicely for me;
          Code:
          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;

          Comment

          Working...
          X