September 25, 2012 at 1:14 pm
Hi all!
I know this have been done before, but that really doesn't help me, 'cause I can't find the solution. Yes, vast google-searching, scanning forums and emailinglists. What ever - if anyone here thinks it's been answered, just link to the solution and I'll be a happy puppy.
Three tables: Orders - OrderArticles (linked by order_id) and Articles (linked by article_id)
- find the necessary sql last in this post.
MISSION: I want to sum the freight grouped by articletype.
The correct return should be:
[highlight]ABC => 160[/highlight] <-- concentrate on this entry
EFG => 80
POL => 110
UTT => 50 UTT => 10 (typo edited)
[h3]What I've tried:[/h3]
-- This sums a wee bit too much. It SUMS for every entry
SELECT a.articletype, SUM(freight)
FROM #orders o
INNER JOIN #orderarticles oa
ON o.id = oa.order_id
INNER JOIN #articles a
ON oa.article_id = a.id
WHERE YEAR(o.orderdate) = 2012
AND MONTH(o.orderdate) = 5
GROUP BY a.articletype
ABC => 330
-- This sums a wee bit too little. It SUMS only the unique freight-values.
SELECT a.articletype, SUM(DISTINCT freight)
FROM #orders o
INNER JOIN #orderarticles oa
ON o.id = oa.order_id
INNER JOIN #articles a
ON oa.article_id = a.id
WHERE YEAR(o.orderdate) = 2012
AND MONTH(o.orderdate) = 5
GROUP BY a.articletype
ABC => 110
[h4]SQL-code:[/h4]
CREATE TABLE #orders (id INT, freight INT, orderdate SMALLDATETIME)
CREATE TABLE #orderarticles (order_id INT, article_id INT, quantity INT)
CREATE TABLE #articles (id INT, articletype VARCHAR(6))
-- working data
INSERT INTO #orders VALUES (1, 20, '2012-04-06')
INSERT INTO #orders VALUES (2, 30, '2012-05-06')
INSERT INTO #orders VALUES (3, 20, '2012-05-07')
INSERT INTO #orders VALUES (4, 50, '2012-05-09')
INSERT INTO #orders VALUES (5, 10, '2012-05-10')
INSERT INTO #orders VALUES (6, 20, '2012-05-12')
INSERT INTO #orders VALUES (7, 50, '2012-05-25')
INSERT INTO #orders VALUES (8, 40, '2012-06-04')
INSERT INTO #articles VALUES (1, 'ABC')
INSERT INTO #articles VALUES (2, 'EFG')
INSERT INTO #articles VALUES (3, 'ABC')
INSERT INTO #articles VALUES (4, 'ABC')
INSERT INTO #articles VALUES (5, 'EFG')
INSERT INTO #articles VALUES (6, 'ABC')
INSERT INTO #articles VALUES (7, 'POL')
INSERT INTO #articles VALUES (8, 'POL')
INSERT INTO #articles VALUES (9, 'POL')
INSERT INTO #articles VALUES (10, 'UTT')
INSERT INTO #articles VALUES (11, 'ABC')
INSERT INTO #articles VALUES (12, 'UTT')
INSERT INTO #orderarticles VALUES (1, 2, 1)
INSERT INTO #orderarticles VALUES (1, 3, 1)
INSERT INTO #orderarticles VALUES (2, 1, 1)
INSERT INTO #orderarticles VALUES (2, 3, 1)
INSERT INTO #orderarticles VALUES (2, 5, 1)
INSERT INTO #orderarticles VALUES (2, 6, 1)
INSERT INTO #orderarticles VALUES (2, 7, 1)
INSERT INTO #orderarticles VALUES (2, 8, 1)
INSERT INTO #orderarticles VALUES (3, 2, 1)
INSERT INTO #orderarticles VALUES (3, 3, 1)
INSERT INTO #orderarticles VALUES (4, 3, 1)
INSERT INTO #orderarticles VALUES (4, 4, 1)
INSERT INTO #orderarticles VALUES (4, 9, 1)
INSERT INTO #orderarticles VALUES (4, 11, 1)
INSERT INTO #orderarticles VALUES (5, 5, 1)
INSERT INTO #orderarticles VALUES (5, 6, 1)
INSERT INTO #orderarticles VALUES (5, 8, 1)
INSERT INTO #orderarticles VALUES (5, 10, 1)
INSERT INTO #orderarticles VALUES (5, 11, 1)
INSERT INTO #orderarticles VALUES (6, 2, 1)
INSERT INTO #orderarticles VALUES (6, 7, 1)
INSERT INTO #orderarticles VALUES (7, 3, 1)
INSERT INTO #orderarticles VALUES (8, 4, 1)
INSERT INTO #orderarticles VALUES (8, 10, 1)
/*
YOUR BRILLIANT SQL SELECT-STATEMENT GOES HERE
*/
DROP TABLE #orders
DROP TABLE #orderarticles
DROP TABLE #articles
September 25, 2012 at 1:35 pm
Excellent job posting ddl and sample data!!!!
I did change up your columns a little bit so it is easier to figure out what is going on.
CREATE TABLE #orders (order_id INT, freight INT, orderdate SMALLDATETIME)
CREATE TABLE #orderarticles (order_id INT, article_id INT, quantity INT)
CREATE TABLE #articles (article_id INT, articletype VARCHAR(6))
The name ID is just too vague for me to keep it straight as it changes name table to table and it is a lot harder to see which ID it is in a select. 😉
I can't figure out where you come up with the value of 160.
Can you explain why 330 is too much? That is the sum of freight values for all 'ABC'. Obviously sum(distinct freight) is logically not at all what you are asking for. If you can explain the logic we can knock out the query pretty easily.
_______________________________________________________________
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/
September 25, 2012 at 1:42 pm
I think I figured it out.
If you analyze the data it's easier to watch what you're trying to do. Thanks for DDL and sample data.
SELECT articletype, SUM( freight) FROM(
SELECT DISTINCT a.articletype, o.id, freight
FROM #orders o
INNER JOIN #orderarticles oa ON o.id = oa.order_id
INNER JOIN #articles a
ON oa.article_id = a.id
WHERE o.orderdate >= '20120501'
AND o.orderdate < '20120601' ) t
GROUP BY articletype
September 25, 2012 at 1:47 pm
in addition to Sean's post.
How do you determine the allocation of the frieght value of an order when the order has multiple atricles.
i.e. Order # 2 has 5 articles (3 ABC's, EFG & POL), but the freight of 30 is at the order level.
Which would you expect to get:
ABC=30 or ABC=90 or ABC=6 (30/5 articles) or ABC=18 (30/5 articles) * 3 ABC's
EFG=30 or EFG=6 (30/5 articles)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 25, 2012 at 4:03 pm
Thank you for your suggestions and prompt replies!
Sean:
I can't figure out where you come up with the value of 160.
Can you explain why 330 is too much?
Jason:
i.e. Order # 2 has 5 articles (3 ABC's, EFG & POL), but the freight of 30 is at the order level.
Which would you expect to get:
ABC=30 or ABC=90 or ABC=6 (30/5 articles) or ABC=18 (30/5 articles) * 3 ABC's
:exclamationmark:This actually got me thinking - there is a lapsus here - what I'm asking for is actually not valid statistics.
But, I'd like to get it anyway - you know what Twain said...
What I want is the freight ONCE for each order AND each articletype.
Jason's example (order #2) would be ABC=30, EFG=30 and POL=30.
Luis sql suggestion fixed me right up. 🙂
I have to investigate it further, but we'll consider this one closed!
Thanks again people!
/ola
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply