SQL 2000 Question

Need help figuring out this problem...

Page 1 of 1

9 Replies - 4701 Views - Last Post: 24 July 2006 - 07:43 PM Rate Topic: -----

#1 kevinm1019  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 09-April 06

SQL 2000 Question

Post icon  Posted 20 July 2006 - 05:51 PM

Hi... I was wondering if you could help me solve a few problems I am having with SQL 2000 and my queries? I am very confused because when I create the following INSERT statement and use the "Return All Rows" function, the results display duplicates??

Here is my code:


insert into tbl_JobTitle
(EEO1_Classification, JobTitle, JobDescription, ExemptNonExempt_Status)
values
('Office/Clerical' , 'Accounting Clerk', 'Computes, classifies, records, and verifies numerical data for use in maintaining
accounting records.', 'Non-Exempt')

insert into tbl_JobTitle
(EEO1_Classification, JobTitle, JobDescription, ExemptNonExempt_Status)
values
('Officials & Managers', 'Assistant Manager', 'Supervises and coordinates activities of workers in department of food store.
Assists store manager in daily operations of store.', 'Exempt')

insert into tbl_JobTitle
(EEO1_Classification, JobTitle, JobDescription, ExemptNonExempt_Status)
values
('Sales Worker', 'Bagger', 'Places customer orders in bags. Performs carryout duties for customers.', 'Non-Exempt')

insert into tbl_JobTitle
(EEO1_Classification, JobTitle, JobDescription, ExemptNonExempt_Status)
values
('Sales Worker', 'Cashier', 'Operates cash register to itemize and total customer’s purchases in grocery
store.', 'Non-Exempt')

insert into tbl_JobTitle
(EEO1_Classification, JobTitle, JobDescription, ExemptNonExempt_Status)
values
('Computer Support Specialist', 'Technician', 'Installs, modifies, and makes minor repairs to personal computer hardware and
software systems, and provides technical assistance and training to system users.', 'Non-Exempt')

insert into tbl_JobTitle
(EEO1_Classification, JobTitle, JobDescription, ExemptNonExempt_Status)
values
('Officials & Managers', 'Director of Finance & Accounting', 'Plans and directs the finance and accounting activities for
 Kudler Fine Foods.', 'Exempt')

insert into tbl_JobTitle
(EEO1_Classification, JobTitle, JobDescription, ExemptNonExempt_Status)
values
('Craft Workers (Skilled)', 'Retail Assistant Bakery & Pastry', 'Obtains or prepares food items requested by customers 
in retail food store.', 'Non-Exempt')

insert into tbl_JobTitle
(EEO1_Classification, JobTitle, JobDescription, ExemptNonExempt_Status)
values
('Operatives (Semi-skilled)', 'Retail Assistant Butchers & Seafood Specialists', 'Obtains or prepares food items requested 
by customers in retail food store.', 'Non-Exempt')

insert into tbl_JobTitle
(EEO1_Classification, JobTitle, JobDescription, ExemptNonExempt_Status)
values
('Office/Clerical', 'Stocker', 'Stores, prices and restocks merchandise displays in store.', 'Non-Exempt')




I have tried re-creating a new database and copying the statements into the new queries... but this did not resolve the issue.

--------------------------------------------------------------------------

Secondly... I am creating JOIN and GROUP statements and would like the last name of the employee to appear in the 'LastName' column. Instead, a count of employees appears in the 'LastName' column. How do I change this? Here is my code:


[b]Query - Group_EEO1_Classification[/b]

select count (*) LastName, EEO1_Classification as EEO1_Classification from tbl_Employee e
group by e.EEO1_Classification
 
[b]Query - Group_Salary within EEO1_Classification[/b]

select count (*) LastName, Salary, EEO1_Classification as EEO1_Classification from tbl_employee 
group by Salary, EEO1_Classification




Any help you can provide will be greatly appreciated. Thank you in advance.

Is This A Good Question/Topic? 0
  • +

Replies To: SQL 2000 Question

#2 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: SQL 2000 Question

Posted 21 July 2006 - 05:18 AM

Your are selcting a count of all records and placing the results in the last name field...you need to separate them.

If counting last names, your query is:
SELECT COUNT(LastName) as LastName, LastName

or if you want a count separate from the name, then
SELECT COUNT(*) as Total, LastName

Was This Post Helpful? 0
  • +
  • -

#3 kevinm1019  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 09-April 06

Re: SQL 2000 Question

Post icon  Posted 21 July 2006 - 10:51 PM

View PostAmadeus, on 21 Jul, 2006 - 04:10 AM, said:

Your are selcting a count of all records and placing the results in the last name field...you need to separate them.

If counting last names, your query is:
SELECT COUNT(LastName) as LastName, LastName

or if you want a count separate from the name, then
SELECT COUNT(*) as Total, LastName



Thank you for the feedback. To make sure I am understanding correctly, the second statement you displayed will actually show the last names of each person in the 'Last Name' column??

This post has been edited by kevinm1019: 21 July 2006 - 10:52 PM

Was This Post Helpful? 0
  • +
  • -

#4 kevinm1019  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 09-April 06

Re: SQL 2000 Question

Posted 22 July 2006 - 11:30 AM

View Postkevinm1019, on 21 Jul, 2006 - 09:43 PM, said:

View PostAmadeus, on 21 Jul, 2006 - 04:10 AM, said:

Your are selcting a count of all records and placing the results in the last name field...you need to separate them.

If counting last names, your query is:
SELECT COUNT(LastName) as LastName, LastName

or if you want a count separate from the name, then
SELECT COUNT(*) as Total, LastName



Thank you for the feedback. To make sure I am understanding correctly, the second statement you displayed will actually show the last names of each person in the 'Last Name' column??


Ok thank you. I'll try it.
Was This Post Helpful? 0
  • +
  • -

#5 kevinm1019  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 09-April 06

Re: SQL 2000 Question

Post icon  Posted 23 July 2006 - 07:18 PM

I tried using the code example given to me, but I ended up getting these results:

LastName Salary Status
3 21000 Exempt
3 10530 Exempt
3 15000 Exempt
3 51000 Exempt
3 21500 Exempt
3 18500 Exempt
3 13500 Exempt
3 25500 Exempt
3 20500 Exempt

What I would like to display is the actual last name of the employee in the 'LastName' column.
Was This Post Helpful? 0
  • +
  • -

#6 kevinm1019  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 09-April 06

Re: SQL 2000 Question

Posted 23 July 2006 - 07:33 PM

The other problem I am having is my results keep producing duplicate information. How do I get rid of that?
Was This Post Helpful? 0
  • +
  • -

#7 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: SQL 2000 Question

Posted 24 July 2006 - 03:47 AM

Can you please post the schema for your tables, and describe exactly what you wish to get out based on those schema?
Was This Post Helpful? 0
  • +
  • -

#8 kevinm1019  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 09-April 06

Re: SQL 2000 Question

Posted 24 July 2006 - 12:19 PM

Ok... what I am doing is creating an employee database. Instead of counting the number of employees and displaying the total counts for each category in the 'LastName' column... I actually want to display the actual employee's last name in the 'LastName' column as it applies in the query statement. For example, if I want to know the names of the exempt vs. non-exempt employees, I don't want the query to count the number of exempt vs. non-exempt... I actually want the results to display the result in the following manner:

Last Name Status
Doe Exempt
Williams Non-Exempt

versus

Last Name Status
2 Exempt
3 Non-Exempt
Was This Post Helpful? 0
  • +
  • -

#9 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: SQL 2000 Question

Posted 24 July 2006 - 12:34 PM

Is the information in different tables? Unless I'm missing something, all you need is
SELECT LastName, Status FROM mytable

Was This Post Helpful? 0
  • +
  • -

#10 kevinm1019  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 09-April 06

Re: SQL 2000 Question

Posted 24 July 2006 - 07:43 PM

No all the data is in the same table. I'll give it a try and let you know how it worked out. Thank you very much.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1