SQL Query - Summarise Data from 2nd table
2

Question

SQL Query - Summarise Data from 2nd table

barnettjacob (IS/IT--Management)

(OP)

29 Dec 11 00:36

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:

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'

If anybody could help me out I would be eternally grateful!
Regards
Jacob

r937 (TechnicalUser) · Answer 1 · 2011-12-29 01:37:59

CODE

SELECT hd.reference1
     , 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

barnettjacob (IS/IT--Management) · Answer 2 · 2011-12-29 17:45:56

Cheers Rudi - I was trying to over complicate things!

Just had to add a couple of lines to get the thing to run (as below).

Thanks a million
Jacob

CODE

SELECT

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

r937 (TechnicalUser) · Answer 3 · 2011-12-29 18:01:41

glad it worked for you

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

barnettjacob (IS/IT--Management) · Answer 4 · 2011-12-30 21:54:14

Rudi,

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))

r937 (TechnicalUser) · Answer 5 · 2011-12-30 22:33:30

i would use windowing functions to do "top" logic in sql server now

that evolt article is getting pretty long in the tooth, eh

CODE

WITH product_net_amt AS (
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

barnettjacob (IS/IT--Management) · Answer 6 · 2011-12-30 23:38:09

RE: SQL Query - Summarise Data from 2nd table

barnettjacob (IS/IT--Management)

(OP)

30 Dec 11 23:38

Thats superb! Thanks Rudy!

PHV (MIS) · Answer 7 · 2012-01-01 09:35:32

And what about this old legacy SQL ?

CODE

SELECT productcode,sum_amt
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?

r937 (TechnicalUser) · Answer 8 · 2012-01-01 10:20:37

looks ok to me, but i can't test it

nice job phv

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

Come Join Us!

Posting Guidelines

SQL Query - Summarise Data from 2nd table
2