SQL Statement Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Statement Help

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-06-20 : 11:22:01

I have a SQL statement that displays 4 Sale's Rep number (slspsn_no) and their total sales for 2 products for the year. Right now it displays like this..

101 1000
102 2000
105 300
110 750

The sales reps are broken up into 4 categories
Domestic = 101, 102
International = 105, 110

How can I make it display like this
Domestic 3000
International 1050

Here is my SQL statement..

SELECT OEHDRHST_SQL.Slspsn_No, SUM(OELINHST2_SQL.Sls_Amt) AS SalesAmount
FROM OELINHST2_SQL INNER JOIN
OEHDRHST_SQL ON OELINHST2_SQL.Ord_No = OEHDRHST_SQL.Ord_No
WHERE (OELINHST2_SQL.Request_Dt BETWEEN '20040101' AND '20041231') AND (OELINHST2_SQL.Prod_Cat IN ('R81', 'R82'))
GROUP BY OEHDRHST_SQL.Slspsn_No

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-20 : 11:55:10

Try:

SELECT
Category = case when OEHDRHST_SQL.Slspsn_No in (101,102) then 'Domestic' when OEHDRHST_SQL.Slspsn_No in (101,102) then 'Domestic' else 'Unknown' end,
SUM(OELINHST2_SQL.Sls_Amt) AS SalesAmount
FROM OELINHST2_SQL INNER JOIN
OEHDRHST_SQL ON OELINHST2_SQL.Ord_No = OEHDRHST_SQL.Ord_No
WHERE (OELINHST2_SQL.Request_Dt BETWEEN '20040101' AND '20041231') AND (OELINHST2_SQL.Prod_Cat IN ('R81', 'R82'))
GROUP BY case when OEHDRHST_SQL.Slspsn_No in (101,102) then 'Domestic' when OEHDRHST_SQL.Slspsn_No in (105,110) then 'Domestic' else 'Unknown' end

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-06-20 : 12:28:56

Hello. Thank you for your quick reply. It gave me an error message saying,

Column OEHDRHST_SQL.Slspsn_No is invalid in the select list because it is not contained in either an aggregate function or the group by clause

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-06-20 : 12:32:27

I changed it to...
SELECT
Category = case when OEHDRHST_SQL.Slspsn_No in (101,102) then 'Domestic' when
OEHDRHST_SQL.Slspsn_No in (101,102) then 'Domestic' else 'Unknown' end,
SUM(OELINHST2_SQL.Sls_Amt) AS SalesAmount
FROM OELINHST2_SQL INNER JOIN
OEHDRHST_SQL ON OELINHST2_SQL.Ord_No = OEHDRHST_SQL.Ord_No
WHERE (OELINHST2_SQL.Request_Dt BETWEEN '20040101' AND '20041231') AND
(OELINHST2_SQL.Prod_Cat IN ('R81', 'R82'))
GROUP BY OEHDRHST_SQL.Slspsn_No

and it works, is that ok what I did? Thanks for your help!

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-06-20 : 12:37:17

One last question, how can i work in

case when OEHDRHST_SQL.Slspsn_No in (105,110) then 'International' when
OEHDRHST_SQL.Slspsn_No in (105,110) then 'Domestic'

in addtion to Domestic

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-20 : 22:04:51

maybe this?:

SELECT
Category = case when OEHDRHST_SQL.Slspsn_No in (101,102) then 'Domestic' when
OEHDRHST_SQL.Slspsn_No in (105,110) then 'International' else 'Unknown' end,
SUM(OELINHST2_SQL.Sls_Amt) AS SalesAmount
FROM OELINHST2_SQL INNER JOIN
OEHDRHST_SQL ON OELINHST2_SQL.Ord_No = OEHDRHST_SQL.Ord_No
WHERE (OELINHST2_SQL.Request_Dt BETWEEN '20040101' AND '20041231') AND
(OELINHST2_SQL.Prod_Cat IN ('R81', 'R82'))
GROUP BY OEHDRHST_SQL.Slspsn_No

Is it giving you 2 records? or 4?

Corey

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-06-20 : 22:10:06

it worked. perfect! thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources