Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    21

    Unanswered: Help with a SQL Query

    Hi, I've been stuck for days so I come here
    to you experts in MySQL.

    I have the following table: (it is a detail of table SALES)

    SALES_DETAIL
    -------------
    {SALE_ID},{PRODUCT_CODE},{QUANTITY},{PRICE},{CLASI FICATION}
    {1},{FP001},{1},{7079.64},{0}
    {2},{CUSB001},{1},{1327.43},{0}
    {2},{FP001},{1},{7079.64},{0}
    {2},{SV001},{1},{5000.00},{1}
    {2},{SV002},{1},{2000.00},{1}

    NOTE: there will be ONLY two clasifications: 0 and 1

    and I need the following output:

    {SALE_ID},{TOTAL_CLAS_0},{TOTAL_CLAS_1}
    {1},{7079.64},{0.00}
    {2},{8407.07},{7000.00}

    At most I've made the following sql:

    select sale_id,
    case clasification when 0 then sum(quantity*price) else 0.00 end as total_clas_0,
    case clasification when 1 then sum(quantity*price) else 0.00 end as total_clas_1,
    from sales_detail
    group by sale_id,clasification

    and the output is:

    {SALE_ID},{TOTAL_CLAS_0},{TOTAL_CLAS_1}
    {1},{7079.64},{0.00}
    {2},{8407.07},{0.00}
    {2},{0.00},{7000.00}

    Please help me with the correct SQL
    Thanks for your time!
    Roland

    By the way: anyone knows how to write here with text alignment?
    I hate using {} symbols to separate values and blank spaces are not
    recognized here. Thanks

  2. #2
    Join Date
    Jul 2004
    Posts
    1

    count classification

    I changed the format of the sales table

    FP001,1,7079.64,0,1
    CUSB001,1,1327.43,0,2
    FP001,1,7079.64,0,2
    SV001,1,5000.00,1,2
    SV002,1,2000.00,1,2

    then issued the following:

    select count(classification), id from sales group by classification;

    This is the result

    count(classification) id
    3 1
    2 2



    Hope this is what you were looking for

    Regards
    Rich
    Last edited by richo778; 07-20-04 at 05:21.

Posting Permissions

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