0

Can any one explain why I'm still getting a separated of 2 same column name even if I already use sum and group by clause?

Here is my query in postgres:

SELECT tbl_boxes.box_name                          AS "BOX NAME",
       tbl_stores.store_name                       AS "STORE NAME",
       tbl_sku.sap_name                            AS "SKU NAME",
       SUM(tbl_bad_orders_product.quantity_pcs)    AS "QUANTITY PER PIECE",
       SUM(tbl_bad_orders_product.price_per_piece) AS "PRICE PER PIECE",
       SUM(tbl_bad_orders_product.quantity_cs)     AS "QUANTITY PER CASE",
       SUM(tbl_bad_orders_product.price_per_case)  AS "PRICE PER CASE",
       tbl_bad_orders_product.reason               AS "REASON",
       SUM(tbl_bad_orders_product.total_amount)    AS "TOTAL AMOUNT",
       tbl_bad_orders_product.date_created         AS "DATE CREATED",
       tbl_bad_orders_product.date_updated         AS "DATE UPDATED",
       tbl_bad_orders_product.date_sync            AS "DATE SYNCED"
FROM tbl_bad_orders_product
       INNER JOIN tbl_users ON tbl_bad_orders_product.tbluserid = tbl_users.tbluserid
       INNER JOIN tbl_boxes ON tbl_bad_orders_product.tblboxid = tbl_boxes.tblboxesid
       INNER JOIN tbl_stores ON tbl_bad_orders_product.tblstoreid = tbl_stores.tblstoreid
       INNER JOIN tbl_sku ON tbl_bad_orders_product.tblskuid = tbl_sku.tblskuid
WHERE tbl_bad_orders_product.date_sync::date >= '2019-02-19'
  AND tbl_bad_orders_product.date_sync::date <= '2019-02-19'
GROUP BY tbl_boxes.box_name, tbl_stores.store_name, tbl_sku.sap_name, tbl_bad_orders_product.quantity_pcs,
         tbl_bad_orders_product.price_per_piece, tbl_bad_orders_product.quantity_cs,
         tbl_bad_orders_product.price_per_case, tbl_bad_orders_product.reason, tbl_bad_orders_product.total_amount,
         tbl_bad_orders_product.date_created, tbl_bad_orders_product.date_updated, tbl_bad_orders_product.date_sync
ORDER BY tbl_boxes.box_name ASC

But im getting this output:

enter image description here

Please help me how to fix it.

Improve this question
9
  • why-not-upload-images-of-code Commented Feb 19, 2019 at 13:24
  • 1
    groupped by tbl_bad_orders_product.price_per_piece Commented Feb 19, 2019 at 13:27
  • @BearBrown already have it in group by clause Commented Feb 19, 2019 at 13:30
  • yes and it is your trouble) Commented Feb 19, 2019 at 13:32
  • @BearBrown how's that become my trouble? Commented Feb 19, 2019 at 13:36

1 Answer 1

Reset to default
1

Essentially, you are including the same aggregated columns in SELECT from tbl_bad_orders (quantity_pcs, price_per_piece, quantity_cs, price_per_case, total_amount) within the GROUP BY clause which almost devoids the need for aggregation since you run an aggregate function, SUM, grouped by its very value.

Simply remove these numeric fields used in SUM aggregation from the GROUP BY clause. Also, consider table aliases for readability and maintainability. Consider also re-ordering fields in SELECT for grouped columns on left hand side and aggregated columns on right hand side of resultset. Finally, your WHERE condition may be redundant.

SELECT b.box_name                  AS "BOX NAME",
       s.store_name                AS "STORE NAME",
       sku.sap_name                AS "SKU NAME",
       bad.reason                  AS "REASON",
       bad.date_created            AS "DATE CREATED",
       bad.date_updated            AS "DATE UPDATED",
       bad.date_sync               AS "DATE SYNCED",
       SUM(bad.quantity_pcs)       AS "QUANTITY PER PIECE",
       SUM(bad.price_per_piece)    AS "PRICE PER PIECE",
       SUM(bad.quantity_cs)        AS "QUANTITY PER CASE",
       SUM(bad.price_per_case)     AS "PRICE PER CASE",
       SUM(bad.total_amount)       AS "TOTAL AMOUNT"
FROM tbl_bad_orders_product AS bad
     INNER JOIN tbl_users AS u ON bad.tbluserid = u.tbluserid
     INNER JOIN tbl_boxes AS b ON bad.tblboxid = b.tblboxesid
     INNER JOIN tbl_stores AS s ON bad.tblstoreid = s.tblstoreid
     INNER JOIN tbl_sku AS sku ON bad.tblskuid = sku.tblskuid
WHERE bad.date_sync::date >= '2019-02-19'
  AND bad.date_sync::date <= '2019-02-19'
GROUP BY b.box_name, 
         s.store_name, 
         sku.sap_name, 
         bad.reason,
         bad.date_created, 
         bad.date_updated, 
         bad.date_sync
ORDER BY b.box_name ASC
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.