Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old January 26th, 2004, 05:21 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default Query Problem group by

I am developing Staff Allocation System,
database is sql server 2000.
I have problem in retrieve the staff informations,

Which employee working which Project and what project have assign to him, what is his assign project or contract no,

One employee working more then one project, retrieve information one employee how many projects are working,
What is his approved position, what is his assign position.
It the main data have to retrieve, as well as retrieve all fields which related to those tables.

I use this query.

select name,apppos approved_position,appcont approved_contract,appdate employee_appr_date,munref Municipality_Ref,dcilref DCIL_REF,projtype Project_Type,strdate Project_str_date,comdate Projcet_comp_date,extdate Proejct_ext_date,dept,emptype Employee_Type from contract,emp,apprecords where contract.rec_id=emp.rec_id and emp.rec_id=apprecords.rec_id and apprecords.name='dewachi'


above query retrieve no data,

how can use group by clause in the above query ?

group by apprecords.appcontract

group by clause give error.

above query have to retrieve data from the three tables, I have four tables, what query I use so that all four tables data retrieve like this.


Name, approved_position, approved_contract,assign_position,assign_contract, startdate,completion_date,,,,,,,,, and so on…
Group by apprecords.appposition
……….


Contract Table (basic data entry contract table)
---------------------------------------
rec_id
Contract No.
ProjectType
StartDate
CompletionDate
ExtendedDate


Employee Table (basic data entry employee table)
----------------------------------
rec_id
EmpNo
Name
Position
Department
EmployeeType


Approved Records Table (in this table all information about
                             the employee and his approved
                                position and contract )
----------------------------------------------
rec_id
Name
Approved Date
MunicipalityRefNo
DCILRefNo
ApprovedPosition
ApprovedContract


Assign Project Table (in this table all information about the
                         employee his assign the project)
-------------------------------------------------------------
rec_id
Name
AssignPosition
AssignContract
EmpProjectStartDate
EmpProjectEndDate
ShiftNo
ProjectStatus


Please help.
how can retrieve the data by four tables using group by clase ?

Regards.

Mateen
  #2 (permalink)  
Old January 27th, 2004, 04:46 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Using group by/having clause, the group by must contain all fields you are selecting other than aggregates. Typically you use the group by with aggregate functions. I hope this helps.
  #3 (permalink)  
Old January 29th, 2004, 04:41 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks for your response.
I use group by clause with having.
query return the data.


select e.empno,e.fname+' '+mname+''+lname,e.posit,contractno,projtype,extda te,dept,bstatus,appstrdate,munref,dcilref,astrdate ,aenddate,cshift,pstatus,a.apppos,aposit,appcont,a ppcont,strdate,comdate
from employees e
inner join approvals a on a.empno=e.empno and a.empno=e.empno
inner join contracts c on c.rec_id=e.rec_id
inner join assignments ap on ap.empno=e.empno
group by e.empno,e.fname+' '+mname+''+lname,e.posit,contractno,projtype,extda te,dept,bstatus,appstrdate,munref,dcilref,astrdate ,aenddate,cshift,pstatus,a.apppos,aposit,appcont,a ppcont,strdate,comdate
having appcont='2016'


regards.

Mateen



  #4 (permalink)  
Old January 30th, 2004, 06:41 AM
Authorized User
 
Join Date: Jan 2004
Location: Pune, MAHARASHTRA, India.
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to ankur_vachhani
Default

U can also user sub-query which will make query more readble and effcient

for example,

SQL> select name from first_table where proj in ( select proj from second_table);


            Hope this may help
  #5 (permalink)  
Old February 4th, 2004, 02:44 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks for your response.

I use following query
this query retrieve all data,
I want to separate the data to use group by clause
in contractno field.
it should retrieve all contractno and
related information from the four tables.


select e.empno,e.fname+' '+mname+''+lname fullname,e.posit,contractno,projtype,contname,cont amt,contr,jobno,strdate,comdate,extdate,dept,bstat us,appsta,aposit,appcont,appstrdate,munref,munletd ate,dcilref,dcilletdate,astrdate,aenddate,cshift,p status,a.apppos,alocat
from employees e
inner join approvals a on a.empno=e.empno and a.empno=e.empno
inner join contracts c on c.rec_id=e.rec_id
inner join assignments ap on ap.empno=e.empno
group by e.empno,e.fname+' '+mname+''+lname, e.posit,contractno,projtype,contname,contamt,contr ,jobno,strdate,comdate,extdate,dept,bstatus,appsta ,aposit,appcont,appstrdate,munref,munletdate,dcilr ef,dcilletdate,astrdate,aenddate,cshift,pstatus,a. apppos,alocat



I want to retrieve the data like

contractno 2016
data ..................

contractno 2015
data.................

contractno 2065
data.................

how can use above query so that data retreive
in group by cluase ?
separate contractno display and data retreive
from the four tables ?

regards.

Mateen



Quote:
quote:Originally posted by bmains
 Using group by/having clause, the group by must contain all fields you are selecting other than aggregates. Typically you use the group by with aggregate functions. I hope this helps.


Similar Threads
Thread Thread Starter Forum Replies Last Post
One Record From Each Group - Query rstelma SQL Server 2000 7 January 3rd, 2008 12:08 AM
SQL query using "Group By" - please help BananaJim SQL Language 2 February 26th, 2007 10:23 AM
SQL query retrieving last record and group by snowy SQL Language 2 December 13th, 2006 01:59 PM
Group by Weeks in a query or table lryckman Access VBA 4 December 4th, 2006 08:55 AM
Group By query with count problem ptaylor SQL Language 2 May 12th, 2004 09:27 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.