×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

SQL Query - Summarise Data from 2nd table
2

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:

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

 

RE: SQL Query - Summarise Data from 2nd table

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

RE: SQL Query - Summarise Data from 2nd table

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

RE: SQL Query - Summarise Data from 2nd table

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

RE: SQL Query - Summarise Data from 2nd table

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

RE: SQL Query - Summarise Data from 2nd table

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

RE: SQL Query - Summarise Data from 2nd table

(OP)
Thats superb! Thanks Rudy!

RE: SQL Query - Summarise Data from 2nd table

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?

RE: SQL Query - Summarise Data from 2nd table

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

nice job phv

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close