How to add a column to a table and count.

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 2005 Forums

Transact-SQL (2005)

How to add a column to a table and count.

Author

Topic

mpfeifle30
Starting Member

2 Posts

Posted - 2009-11-02 : 14:32:04

Add a column in the Rep table called CustomerCount that counts how many customers the given representative serves.

Here is the SQL code:

http://galati.armstrong.edu/itec3500/data/SQL/Premiere.sql

Any help would be greatly appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-02 : 15:09:03

I don't like downloading people's script files from the Internet. Could you instead post the SQL code directly here?

You shouldn't add a column to a table like this. Instead, dynamically retrieve the value when you need it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

mpfeifle30
Starting Member

2 Posts

Posted - 2009-11-02 : 15:13:15

CREATE TABLE Rep
(RepNum CHAR(2) PRIMARY KEY,
LastName CHAR(15),
FirstName CHAR(15),
Street CHAR(15),
City CHAR(15),
State CHAR(2),
Zip CHAR(5),
Commission DECIMAL(7,2),
Rate DECIMAL(3,2) )
;

CREATE TABLE Customer
(CustomerNum CHAR(3) PRIMARY KEY,
CustomerName CHAR(35) NOT NULL,
Street CHAR(15),
City CHAR(15),
State CHAR(2),
Zip CHAR(5),
Balance DECIMAL(8,2),
CreditLimit DECIMAL(8,2),
RepNum CHAR(2) )
;

CREATE TABLE Orders
(OrderNum CHAR(5) PRIMARY KEY,
OrderDate DATETIME,
CustomerNum CHAR(3) )
;

CREATE TABLE Part
(PartNum CHAR(4) PRIMARY KEY,
Description CHAR(15),
OnHand DECIMAL(4,0),
Class CHAR(2),
Warehouse CHAR(1),
Price DECIMAL(6,2) )
;

CREATE TABLE OrderLine
(OrderNum CHAR(5),
PartNum CHAR(4),
NumOrdered DECIMAL(3,0),
QuotedPrice DECIMAL(6,2),
PRIMARY KEY (OrderNum, PartNum) )
;

INSERT INTO Rep
VALUES
('20','Kaiser','Valerie','624 Randall','Grove','FL','33321',20542.50,0.05);
INSERT INTO Rep
VALUES
('35','Hull','Richard','532 Jackson','Sheldon','FL','33553',39216.00,0.07);
INSERT INTO Rep
VALUES
('65','Perez','Juan','1626 Taylor','Fillmore','FL','33336',23487.00,0.05);
INSERT INTO Customer
VALUES
('148','Al''s Appliance and Sport','2837 Greenway','Fillmore','FL','33336',6550.00,7500.00,'20');
INSERT INTO CUSTOMER
VALUES
('282','Brookings Direct','3827 Devon','Grove','FL','33321',431.50,10000.00,'35');
INSERT INTO CUSTOMER
VALUES
('356','Ferguson''s','382 Wildwood','Northfield','FL','33146',5785.00,7500.00,'65');
INSERT INTO CUSTOMER
VALUES
('408','The Everything Shop','1828 Raven','Crystal','FL','33503',5285.25,5000.00,'35');
INSERT INTO CUSTOMER
VALUES
('462','Bargains Galore','3829 Central','Grove','FL','33321',3412.00,10000.00,'65');
INSERT INTO CUSTOMER
VALUES
('524','Kline''s','838 Ridgeland','Fillmore','FL','33336',12762.00,15000.00,'20');
INSERT INTO CUSTOMER
VALUES
('608','Johnson''s Department Store','372 Oxford','Sheldon','FL','33553',2106.00,10000.00,'65');
INSERT INTO CUSTOMER
VALUES
('687','Lee''s Sport and Appliance','282 Evergreen','Altonville','FL','32543',2851.00,5000.00,'35');
INSERT INTO CUSTOMER
VALUES
('725','Deerfield''s Four Seasons','282 Columbia','Sheldon','FL','33553',248.00,7500.00,'35');
INSERT INTO CUSTOMER
VALUES
('842','All Season','28 Lakeview','Grove','FL','33321',8221.00,7500.00,'20');
INSERT INTO ORDERS
VALUES
('21608','20-OCT-2010','148');
INSERT INTO ORDERS
VALUES
('21610','20-OCT-2010','356');
INSERT INTO ORDERS
VALUES
('21613','21-OCT-2010','408');
INSERT INTO ORDERS
VALUES
('21614','21-OCT-2010','282');
INSERT INTO ORDERS
VALUES
('21617','23-OCT-2010','608');
INSERT INTO ORDERS
VALUES
('21619','23-OCT-2010','148');
INSERT INTO ORDERS
VALUES
('21623','23-OCT-2010','608');
INSERT INTO PART
VALUES
('AT94','Iron',50,'HW','3',24.95);
INSERT INTO PART
VALUES
('BV06','Home Gym',45,'SG','2',794.95);
INSERT INTO PART
VALUES
('CD52','Microwave Oven',32,'AP','1',165.00);
INSERT INTO PART
VALUES
('DL71','Cordless Drill',21,'HW','3',129.95);
INSERT INTO PART
VALUES
('DR93','Gas Range',8,'AP','2',495.00);
INSERT INTO PART
VALUES
('DW11','Washer',12,'AP','3',399.99);
INSERT INTO PART
VALUES
('FD21','Stand Mixer',22,'HW','3',159.95);
INSERT INTO PART
VALUES
('KL62','Dryer',12,'AP','1',349.95);
INSERT INTO PART
VALUES
('KT03','Dishwasher',8,'AP','3',595.00);
INSERT INTO PART
VALUES
('KV29','Treadmill',9,'SG','2',1390.00);
INSERT INTO OrderLine
VALUES
('21608','AT94',11,21.95);
INSERT INTO OrderLine
VALUES
('21610','DR93',1,495.00);
INSERT INTO OrderLine
VALUES
('21610','DW11',1,399.99);
INSERT INTO OrderLine
VALUES
('21613','KL62',4,329.95);
INSERT INTO OrderLine
VALUES
('21614','KT03',2,595.00);
INSERT INTO OrderLine
VALUES
('21617','BV06',2,794.95);
INSERT INTO OrderLine
VALUES
('21617','CD52',4,150.00);
INSERT INTO OrderLine
VALUES
('21619','DR93',1,495.00);
INSERT INTO OrderLine
VALUES
('21623','KV29',2,1290.00);

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-11-02 : 15:17:33

select * from rep r cross apply(select COUNT(*)cnt from customer c where c.repnum=r.repnum)c

Hope can help...but advise to wait pros with confirmation...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-11-02 : 15:18:39

win tara

Hope can help...but advise to wait pros with confirmation...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-02 : 15:57:54

You can't win unless you have an opponent.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-11-02 : 18:40:00

Hooray for solving people's homework problems. The link to the script posted is a .edu - hilarious.

Some days you're the dog, and some days you're the fire hydrant.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources