Professional Web Applications Themes

SUM (DISTINCT) INNER JOIN - MySQL

I have two tables recording the sales transactions with purchase_id the common key Table 1 (Recording total amounts and customer info) Purchase_Id (Primary Key) Sales_Total (Total sales amount) Customer_Id ..... (Other fields) Table 2 (Recording the detail transaction items) Purchase_Id (Primary Key) Product_Id (Product information) ...... (Other fields) Here is a sample record set of my table Table 1 Purchase_Id Sales_Total Customer_Id 1 200 1 2 300 4 3 500 4 ................. Table 2 Purchase_Id Product_Id 1 1001 1 1005 2 1004 3 1001 3 1002 3 1003 ..................... I need to generate the sales report and caculate the total ...

  1. #1

    Default SUM (DISTINCT) INNER JOIN

    I have two tables recording the sales transactions with purchase_id the
    common key

    Table 1 (Recording total amounts and customer info)

    Purchase_Id (Primary Key)
    Sales_Total (Total sales amount)
    Customer_Id
    ..... (Other fields)

    Table 2 (Recording the detail transaction items)
    Purchase_Id (Primary Key)
    Product_Id (Product information)
    ...... (Other fields)

    Here is a sample record set of my table

    Table 1
    Purchase_Id Sales_Total Customer_Id
    1 200 1
    2 300 4
    3 500 4

    .................

    Table 2
    Purchase_Id Product_Id
    1 1001
    1 1005
    2 1004
    3 1001
    3 1002
    3 1003
    .....................
    I need to generate the sales report and caculate the total sales of a
    particular customer

    Joining these two tables result in
    Purhcase_Id Sales_Total Customer_Id Product_Id
    1 200 1 1001
    1 200 1 1005
    2 300 4 1004
    3 500 4 1001
    3 500 4 1002
    3 500 4 1003

    I am using Mysql Database, I did select statement like this:
    SELECT Customer_Id, SUM(Sales_Total) FROM Table 1 INNER JOIN Table 2
    USING (Purchase_ID) WHERE Product_Id > 1000 GROUP BY Customer_Id

    Result In
    Customer_Id SUM(Sales_Total)
    1 400
    4 1800

    Which is not correct, I want to count on UNIQUE (DISTINCT) Purchsae_Id
    and no need to duplicately sum up on rows.

    I.e, the result should be
    Customer_Id SUM
    1 200
    4 800

    What should I do???


    Thanks.

    Edward Guest

  2. #2

    Default Re: SUM (DISTINCT) INNER JOIN

    "Edward" <edwardcppoon> wrote in message
    news:1134565715.763651.162170g14g2000cwa.googlegr oups.com...
    > Table 1
    > Purchase_Id Sales_Total Customer_Id
    > 1 200 1
    > 2 300 4
    > 3 500 4
    >
    > ................
    >
    > Table 2
    > Purchase_Id Product_Id
    > 1 1001
    > 1 1005
    > 2 1004
    > 3 1001
    > 3 1002
    > 3 1003
    >
    > I am using Mysql Database, I did select statement like this:
    > SELECT Customer_Id, SUM(Sales_Total) FROM Table 1 INNER JOIN Table 2
    > USING (Purchase_ID) WHERE Product_Id > 1000 GROUP BY Customer_Id
    >
    > Result In
    > Customer_Id SUM(Sales_Total)
    > 1 400
    > 4 1800
    >
    > Which is not correct, I want to count on UNIQUE (DISTINCT) Purchsae_Id
    > and no need to duplicately sum up on rows.
    >
    > I.e, the result should be
    > Customer_Id SUM
    > 1 200
    > 4 800
    >
    > What should I do???
    For what it's worth, your problem stems from an improperly designed schema.
    You should store the per-lineitem price as a field in Table2, instead of
    storing the total price of the purchase in Table1. Then your SUM()
    computation would be correct. But I understand that you might not have the
    price of each line-item handy when you create these rows. Also it might be
    adjusted for sales tax, etc.

    Try this:

    SELECT J.Customer_Id, SUM(J.Sales_Total)
    FROM (SELECT DISTINCT Customer_Id, Sales_Total, Purchase_Id
    FROM Table1 INNER JOIN Table2 USING (Purchase_Id)
    WHERE Product_Id > 1000) AS J
    GROUP BY J.Customer_Id

    You need to use MySQL 4.1 or later to use this subquery syntax.

    Regards,
    Bill K.


    Bill Karwin Guest

  3. #3

    Default Re: SUM (DISTINCT) INNER JOIN

    Thanks for your information.

    You're right that the reason why i need to design the schema like this
    is because there might be adjustments in the total purchase due to
    discount offer or tax in which it's hard to determine by program that
    how i should "distribute" the discount amount into per item prices.


    Bill Karwin 寫道:
    > "Edward" <edwardcppoon> wrote in message
    > news:1134565715.763651.162170g14g2000cwa.googlegr oups.com...
    > > Table 1
    > > Purchase_Id Sales_Total Customer_Id
    > > 1 200 1
    > > 2 300 4
    > > 3 500 4
    > >
    > > ................
    > >
    > > Table 2
    > > Purchase_Id Product_Id
    > > 1 1001
    > > 1 1005
    > > 2 1004
    > > 3 1001
    > > 3 1002
    > > 3 1003
    > >
    > > I am using Mysql Database, I did select statement like this:
    > > SELECT Customer_Id, SUM(Sales_Total) FROM Table 1 INNER JOIN Table 2
    > > USING (Purchase_ID) WHERE Product_Id > 1000 GROUP BY Customer_Id
    > >
    > > Result In
    > > Customer_Id SUM(Sales_Total)
    > > 1 400
    > > 4 1800
    > >
    > > Which is not correct, I want to count on UNIQUE (DISTINCT) Purchsae_Id
    > > and no need to duplicately sum up on rows.
    > >
    > > I.e, the result should be
    > > Customer_Id SUM
    > > 1 200
    > > 4 800
    > >
    > > What should I do???
    >
    > For what it's worth, your problem stems from an improperly designed schema.
    > You should store the per-lineitem price as a field in Table2, instead of
    > storing the total price of the purchase in Table1. Then your SUM()
    > computation would be correct. But I understand that you might not have the
    > price of each line-item handy when you create these rows. Also it might be
    > adjusted for sales tax, etc.
    >
    > Try this:
    >
    > SELECT J.Customer_Id, SUM(J.Sales_Total)
    > FROM (SELECT DISTINCT Customer_Id, Sales_Total, Purchase_Id
    > FROM Table1 INNER JOIN Table2 USING (Purchase_Id)
    > WHERE Product_Id > 1000) AS J
    > GROUP BY J.Customer_Id
    >
    > You need to use MySQL 4.1 or later to use this subquery syntax.
    >
    > Regards,
    > Bill K.
    Edward Guest

  4. #4

    Default Re: SUM (DISTINCT) INNER JOIN

    Have checked and unfortunately my MYSQL version is 4.0.18 which is not
    supported. Checking reference to re-write the SQL to perform the same
    job and no glues, should I give it up and try to capture the distinct
    sql statement result then do the sum up in application level? (Which I
    think myself stupid!)

    Edward Guest

  5. #5

    Default Re: SUM (DISTINCT) INNER JOIN

    "Edward" <edwardcppoon> wrote in message
    news:1134648263.943557.12690g14g2000cwa.googlegro ups.com...
    > Have checked and unfortunately my MYSQL version is 4.0.18 which is not
    > supported. Checking reference to re-write the SQL to perform the same
    > job and no glues, should I give it up and try to capture the distinct
    > sql statement result then do the sum up in application level? (Which I
    > think myself stupid!)
    You could also create a temporary table in the MySQL database, fill it with
    the results from the "distinct" query, and then run the aggregate query
    against that table.

    I encourage you to upgrade to a more recent version of MySQL if possible.
    However, I understand if it is not up to you.

    Regards,
    Bill K.


    Bill Karwin Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. need help on DISTINCT
    By Krechting in forum ASP Database
    Replies: 1
    Last Post: January 12th, 01:07 AM
  3. Replies: 3
    Last Post: April 18th, 12:52 PM
  4. Replies: 0
    Last Post: April 15th, 01:22 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139