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

COUNT

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-04 : 11:54:00

David writes "I have a question, I have seen your posting and I guess I am asking for help

What I need is to get the total COUNT on the sales rep, with using the (postal code) I am using substring to extract the first 3 Like substring(postalcode,1,3) but I am running in to problems, the count is returning a very high number..
Our postal code in Canada is like this L8K 2W3 I only need the L8K. I can count that, but when I have to match it with a salesrep I am getting screwed up.

As you can tell I am JR. But trying hard. I have also used outter joins to make it work but this seems to be working...well maybe not...

I am dealing with 3 tables here there are.

SALES

1	SALES_ID	numeric	5	0
0	Salesrep_ID	numeric	5	0
0	BU_ID	numeric	5	0
0	Customer_ID	numeric	5	0
0	[YEAR]	int	4	-1
0	PERIOD1	numeric	5	1
0	PERIOD2	numeric	5	1
0	PERIOD3	numeric	5	1
0	PERIOD4	numeric	5	1
0	PERIOD5	numeric	5	1
0	PERIOD6	numeric	5	1
0	PERIOD7	numeric	5	1
0	PERIOD8	numeric	5	1
0	PERIOD9	numeric	5	1
0	PERIOD10	numeric	5	1
0	PERIOD11	numeric	5	1
0	PERIOD12	numeric	5	1
0	COMMENT	varchar	255	1
0	CURR_Salesrep_ID	numeric	5	1
0	CURR_BU_ID	numeric	5	1

CUSTOMER

3	CUSTOMER_ID	numeric	9	0
0	BU_ID	        numeric	9	1
0	SALESREP_ID	numeric	9	1
0	CUSTNO	        varchar	6	1
0	CUST_TYPE_ID	numeric	9	1
0	COMPANY	        varchar	50	1
0	POSTALCODE	varchar	10	1
0	BILL_ACCT	varchar	8	1
0	ADDRESS1	varchar	35	1
0	ADDRESS2	varchar	35	1
0	CITY	varchar	20	1
0	PROV	varchar	10	1
0	COUNTRY	varchar	15	1
0	PHONE	varchar	20	1
0	PHONE2	varchar	20	1

SALESREP

3	SALESREP_ID	numeric	9	0
0	BU_ID	        numeric	9	1
0	SALESREP	varchar	6	1
0	REPNAME	        varchar	50	1

Hope you get it! :) here is my query...

select distinct  c.custno,
c.company, substring(c.postalcode,1,3) Postal, 
		(select distinct count(c.postalcode) from customer c, salesrep sr, sales s
		 where c.salesrep_id = 1215
		 and s.salesrep_id = c.salesrep_id
		 and s.year = 2000) Postal_Cnt,
--group by c.postalcode, s.salesrep_id),

s.sales_id, sr.salesrep, s.salesrep_id, s.year, sr.repname
from salesrep sr, customer c, sales s
where sr.salesrep = 'WGA'
and s.year = 2000
and sr.salesrep_id = c.salesrep_id
and c.salesrep_id = s.salesrep_id
and sr.bu_id = c.bu_id
and c.bu_id = s.bu_id
and c.customer_id = s.customer_id

Hope you can help! :) "

Rafiq
Starting Member

25 Posts

Posted - 2002-01-05 : 00:17:49

Hi David,

I analysed ur pblm. Just add a join condition between Customer table and Sales table (s.Customer_ID = c.Customer_ID) in derived table query part. If you do it, I think your pblm will be solved.

I want to tell a suggestion to you. If you are using GROUP BY clause in this query part, it will raise a error message like (Subquery returned more than 1 value...). So, Analyse your query once again.

Regards,

Rafiq

Nazim
A custom title

1408 Posts

Posted - 2002-01-05 : 00:30:10

You need to join all the three tables in your subquery
Sales,customer,salesrep with your common keys.

As for as the Group by clause Rafiq has raised a valid point. but i think u already commented it out.

HTH

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Rafiq
Starting Member

25 Posts

Posted - 2002-01-05 : 01:08:15

Hi Nazim,

Thanks for your acceptence. That commented line may be used in future, It will raise a error. thats all.

Regards,

Rafiq

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources