Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011
    Posts
    1

    Unanswered: Newbee help with query

    Hi all,
    I have a query that gives a usable result but I would like to refine the results even further but Im at a loss how to proceed.

    Select distinct substr(ab.clientarticleno,1,6) as sku, count(t.idtsu) as tsucount
    From articlebase ab, artintsu ait, tsu t
    Where t.warehouse = 1
    And area = 60
    And ait.idarticle = ab.idarticle
    And t.idtsu = ait.idtsu
    Group by ab.clientarticleno
    Order by 2 desc

    Result:
    Sku tsucount
    123456 11
    134532 11
    213456 6
    312456 6
    Etc.......(22,000 records)

    I would like to display the results by tsucount(quantity) and group them by i.e. 1-3,4-6,7-10,11-15,20+

    New Result
    Quantity Tsu
    1-3 14,789 (14,789 tsu with a tsucount of 1-3)
    4-6 4,897
    7-10 1,453
    Etc....

    any help would be appreciated.

  2. #2
    Join Date
    Oct 2011
    Posts
    3
    Sorry not a ton of time today but I believe you want to break this data into case's. Something along these lines applied into your query should do the trick:

    SELECT
    SUM(CASE WHEN (t.idtsu) BETWEEN 0 AND 4 then 1 else 0 end)"1-3",
    SUM(CASE WHEN (t.idtsu) BETWEEN 3 AND 7 then 1 else 0 end)"4-6",
    SUM(CASE WHEN (t.idtsu) BETWEEN 6 AND 11 then 1 else 0 end)"7-10",
    SUM(CASE WHEN (t.idtsu) BETWEEN 10 AND 16 then 1 else 0 end)"11-15",
    SUM(CASE WHEN (t.idtsu) BETWEEN 15 AND 20 then 1 else 0 end)"16-19",
    SUM(CASE WHEN (t.idtsu) >= 20 then 1 else 0 end)"20+"
    FROM articlebase ab, artintsu ait, tsu t
    WHERE t.warehouse = 1
    AND area = 60
    AND ait.idarticle = ab.idarticle
    AND t.idtsu = ait.idtsu
    AND t.idtsu IS NOT NULL;
    Last edited by CapC; 10-25-11 at 14:56.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •