November 28, 2017 at 7:10 am
SELECT id_product, SUM(COALESCE(received_qty, ordered_qty)) AS total
FROM (Select id_department,id_product,received_qty, ordered_qty from
(SELECT * FROM cart_item WHERE id_cart IN (SELECT id_cart FROM purchase)) AS c
LEFT JOIN Purchase p on p.id_cart = c.id_cart ) as Z
GROUP BY id_product
http://sqlfiddle.com/#!9/58cdd3/1
I am trying to get the total sum of qty from ordered_qty and received_qty, but it only sum the id_cart = 1 and ignored other id_cart.
What's wrong with my query? Thanks.
expected output : show total of quantity based on id_product from different purchase.
November 28, 2017 at 7:19 am
In your sql fiddle it has two rows in the result product id 1 and 2. I would also point out that your sql fiddle is created for mysql. This is a sql server forum.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 28, 2017 at 7:23 am
Sorry, but it is missing product id 3.
November 28, 2017 at 7:55 am
What are you trying to accomplish? Your query seems overly complicated for the output you are receiving.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 28, 2017 at 8:01 am
Not really sure what you are trying to do here but I think you could simplify your query to something like this.
select id_product
, Total = SUM(COALESCE(received_qty, ordered_qty))
from Cart_Item
group by id_product
You were not seeing id_product 3 in your results because you have no purchase for id_cart of 2.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 28, 2017 at 8:05 am
I am doing analysis for the quantity of product purchased. I have few table to join to get other details from the table. As you can see from the sql fiddle, i am trying to sum the quantity of product received, and if quantity received is null then will get the value of ordered quantity. The output show is incorrect since it only shows me the id cart = 1. By right i want it to show both cart id 1 and 2 and sum of quantity of product id..
November 28, 2017 at 8:13 am
testingpoke1 - Tuesday, November 28, 2017 8:05 AMI am doing analysis for the quantity of product purchased. I have few table to join to get other details from the table. As you can see from the sql fiddle, i am trying to sum the quantity of product received, and if quantity received is null then will get the value of ordered quantity. The output show is incorrect since it only shows me the id cart = 1. By right i want it to show both cart id 1 and 2 and sum of quantity of product id..
Well look at your query (which is way more complicated than it needs to be). You have:
SELECT * FROM cart_item WHERE id_cart IN (SELECT id_cart FROM purchase)
This by definition will prevent id_product 3 from appearing in your results because you do not have a row in the purchase table for id_cart 2. I am willing to help you but you have to explain what it is you are trying to do here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 28, 2017 at 8:31 am
Process: - When user buy item and check out then there's cart and cart items table to store the transaction.
- 1 cart_id have many item which stored in cart items table.
- After purchase succeed, then will generate a purchase order id and stored in purchase order table .
- In purchase order table, id_cart and status will be stored.
From here, i am trying to calculate quantity based on id_product or id branch or etc from the purchase made.
There is receiving and ordered quantity field, which in some cases quantity received field might be null, so i will take ordered quantity value.
cart item table
cart table
purchase order table
November 28, 2017 at 8:37 am
testingpoke1 - Tuesday, November 28, 2017 8:31 AMProcess: - When user buy item and check out then there's cart and cart items table to store the transaction.
- 1 cart_id have many item which stored in cart items table.
- After purchase succeed, then will generate a purchase order id and stored in purchase order table .
- In purchase order table, id_cart and status will be stored.From here, i am trying to calculate quantity based on id_product or id branch or etc from the purchase made.
There is receiving and ordered quantity field, which in some cases quantity received field might be null, so i will take ordered quantity value.
So why do you think id_product 3 should be in your results? You have no row in the purchase table for id_cart 2.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 28, 2017 at 8:39 am
just a thought....it may help explain
SELECT c.id_product,
SUM(COALESCE(c.received_qty, c.ordered_qty)) AS Total,
c.id_cart,
p.id_purchase,
p.id_department
FROM Cart_Item AS c
LEFT OUTER JOIN Purchase AS p ON c.id_cart = p.id_cart
GROUP BY c.id_product,
c.id_cart,
p.id_purchase,
p.id_department;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 28, 2017 at 9:06 am
Sorry, that's was my mistake. This is my currently query.where it show inaccurate result.
In my case, if the there is 2 record for same product id, where quantity = 1 for cart id 1 and 2, then it will show total = 1 in the result, else when the record for same product id where quantity diffrently for e.g qty = 1 for card id 1 and qty = 2 for cart id 2 then the total will sum up and became 3.
SELECT id_product,sum(DISTINCT(COALESCE(received_qty, quantity)))
FROM (SELECT C.id_cart,C.received_qty,C.quantity , P.id_product,
PO.id_purchase_order, PO.status
FROM (SELECT * FROM cart_items WHERE id_cart IN (SELECT id_cart FROM purchase_orders)) AS C
LEFT JOIN products as P on p.id_product = c.id_product
LEFT JOIN purchase_orders AS PO ON C.id_cart = PO.id_cart ) AS A
GROUP By A.id_product
I tried this query where it only show me
By right, there should be product id 1212 = qty 1 and 1223 = qty 2
November 28, 2017 at 9:22 am
testingpoke1 - Tuesday, November 28, 2017 9:06 AMSorry, that's was my mistake. This is my currently query.where it show inaccurate result.
In my case, if the there is 2 record for same product id, where quantity = 1 for cart id 1 and 2, then it will show total = 1 in the result, else when the record for same product id where quantity diffrently for e.g qty = 1 for card id 1 and qty = 2 for cart id 2 then the total will sum up and became 3.
SELECT id_product,sum(DISTINCT(COALESCE(received_qty, quantity)))
FROM (SELECT C.id_cart,C.received_qty,C.quantity , P.id_product,
PO.id_purchase_order, PO.status
FROM (SELECT * FROM cart_items WHERE id_cart IN (SELECT id_cart FROM purchase_orders)) AS C
LEFT JOIN products as P on p.id_product = c.id_product
LEFT JOIN purchase_orders AS PO ON C.id_cart = PO.id_cart ) AS A
GROUP By A.id_productI tried this query where it only show me
By right, there should be product id 1212 = qty 1 and 1223 = qty 2
This no longer matches the sample data you posted. Help us to help you by providing all the details.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 28, 2017 at 9:30 am
As you can see the result get from the query, total of the quantity for both id_product should be 3 where product id for 1223 =2 and 1212 =1. But the result i get from the query above show me product id 1212 =1 and 1223 =1.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply