Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Query - Summarise Data from 2nd table 2

Status
Not open for further replies.
barnettjacob

barnettjacob

IS-IT--Management
Joined
Aug 12, 2011
Messages
31
Location
GB
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

 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Rudi,

You wrote an article some years ago on an SQL for returning the top 10 ranked here:
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))
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
FAQ181-2886
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question Question
windows command in a sql script
Replies
1
Views
606
carp
carp
mmiah1982
  • Locked
  • Question Question
Extracting mutiple data items from DB with JSON string format column
Replies
2
Views
563
mmiah1982
mmiah1982
fredong
  • Locked
  • Question Question
Update upper row data with next row data that was detached partially from import.
Replies
6
Views
828
carp
carp
Tetol
  • Locked
  • Question Question
Update script is changing the number of records for the values updated each time I run the query
Replies
1
Views
588
carp
carp
mikrom
  • Locked
  • Question Question
from a table with symmetric relation select every pair only once
Replies
0
Views
439
mikrom
mikrom

Part and Inventory Search

Sponsor

Back
Top