SQL Query - Summarise Data from 2nd table
SQL Query - Summarise Data from 2nd table
(OP)
Hi, hoping someone can help me with a query I need to write to summarise the components of a retail transaction where the transaction details are in one table (saleheader) but the details are in the other (saleline).
My aim is a query that, for each transaction (reference1) on the saleheader table, returns the sum of sales for each Division (analysis1code) which is stored in the saleline table.
Reference1 is the link between the two tables.
My attempt (which doesn't come close to working) was:
If anybody could help me out I would be eternally grateful!
Regards
Jacob
My aim is a query that, for each transaction (reference1) on the saleheader table, returns the sum of sales for each Division (analysis1code) which is stored in the saleline table.
Reference1 is the link between the two tables.
My attempt (which doesn't come close to working) was:
CODE
select
reference1,
branchcode,
(select sum(case when analysis1code = 'baby' then (fcextendednetamount-fcextendedtaxamount) else 0 end) from eee..vwsaleline_gp) as 'H&T',
(select sum(case when analysis1code = 'fashion' then (fcextendednetamount-fcextendedtaxamount) else 0 end) from eee..vwsaleline_gp) as 'Apparel',
(select sum(case when analysis1code = 'giftware' then (fcextendednetamount-fcextendedtaxamount) else 0 end) from eee..vwsaleline_gp) as 'T&G'
from eee..vwsaleheader_gp
where saledate >= '12/01/11'
reference1,
branchcode,
(select sum(case when analysis1code = 'baby' then (fcextendednetamount-fcextendedtaxamount) else 0 end) from eee..vwsaleline_gp) as 'H&T',
(select sum(case when analysis1code = 'fashion' then (fcextendednetamount-fcextendedtaxamount) else 0 end) from eee..vwsaleline_gp) as 'Apparel',
(select sum(case when analysis1code = 'giftware' then (fcextendednetamount-fcextendedtaxamount) else 0 end) from eee..vwsaleline_gp) as 'T&G'
from eee..vwsaleheader_gp
where saledate >= '12/01/11'
If anybody could help me out I would be eternally grateful!
Regards
Jacob
Talk To Other Members
RE: SQL Query - Summarise Data from 2nd table
CODE
, hd.branchcode
, SUM(CASE WHEN ln.analysis1code = 'baby'
THEN ln.fcextendednetamount - ln.fcextendedtaxamount
ELSE 0 END) AS 'H&T'
, SUM(CASE WHEN analysis1code = 'fashion'
THEN ln.fcextendednetamount - ln.fcextendedtaxamount
ELSE 0 END) AS 'Apparel'
, SUM(CASE WHEN analysis1code = 'giftware'
THEN ln.fcextendednetamount - ln.fcextendedtaxamount
ELSE 0 END) AS 'T&G'
FROM eee..vwsaleheader_gp AS hd
INNER
JOIN eee..vwsaleline_gp AS ln
WHERE hd.saledate >= '12/01/11'
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: SQL Query - Summarise Data from 2nd table
Just had to add a couple of lines to get the thing to run (as below).
Thanks a million
Jacob
CODE
hd.reference1,
hd.branchcode,
SUM(CASE WHEN ln.analysis1code in ('mc h&t','baby','home')THEN ln.fcextendednetamount - ln.fcextendedtaxamount ELSE 0 END) AS 'H&T',
SUM(CASE WHEN analysis1code in ('mcclothing', 'fashion' ) THEN ln.fcextendednetamount - ln.fcextendedtaxamount ELSE 0 END) AS 'Apparel'
SUM(CASE WHEN analysis1code in ('elc', 'giftware') THEN ln.fcextendednetamount - ln.fcextendedtaxamount ELSE 0 END) AS 'T&G'
FROM eee..vwsaleheader_gp AS hd
INNER
JOIN eee..vwsaleline_gp AS ln on hd.reference1 = ln.reference1
WHERE hd.saledate >= '12/29/11' and hd.saledate < '12/30/11'
group by hd.reference1,hd.branchcode
RE: SQL Query - Summarise Data from 2nd table
fyi i don't think those date formats are compatible with ANSI SQL but then your database obviously knows how to understand them anyway
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: SQL Query - Summarise Data from 2nd table
You wrote an article some years ago on an SQL for returning the top 10 ranked here: http://www.evolt.org/node/131
I'm keen to do something similar but aggregating the sales by product to give me the top 200 sellers. I modified you code (below) in a way that seemed intuitive however it won't run citing an objection to the aggregation! FYI I'm using SQL Server 2008.
Any guidance would be most appreciated.
Regards
Jacob
SELECT
productcode,
sum(fcextendednetamount)
from eee..vwsaleline_gp s
WHERE 200 > (
SELECT COUNT(*)
FROM eee..vwsaleline_gp
WHERE sum(fcextendednetamount) > sum(s.fcextendednetamount))
RE: SQL Query - Summarise Data from 2nd table
that evolt article is getting pretty long in the tooth, eh
CODE
SELECT productcode
, sum_amt
, ROW_NUMBER() OVER
( ORDER BY sum_amt DESC ) AS row
FROM ( SELECT productcode
, SUM(fcextendednetamount) AS sum_amt
FROM eee..vwsaleline_gp
GROUP
BY productcode ) AS d
)
SELECT *
FROM product_net_amt
WHERE row <= 200
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: SQL Query - Summarise Data from 2nd table
RE: SQL Query - Summarise Data from 2nd table
CODE
FROM (
SELECT productcode,SUM(fcextendednetamount) AS sum_amt
FROM eee..vwsaleline_gp GROUP BY productcode
) S
WHERE 200 > (
SELECT COUNT(*) FROM (
SELECT productcode,SUM(fcextendednetamount)
FROM eee..vwsaleline_gp GROUP BY productcode
HAVING SUM(fcextendednetamount) > S.sum_amt) D
)
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: SQL Query - Summarise Data from 2nd table
nice job phv
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon