SELECT DISTINCT Problem - 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)

SELECT DISTINCT Problem

Author

Topic

pshearer
Starting Member

2 Posts

Posted - 2007-02-01 : 08:10:37

I have tried to solve this problem myself and have had a look on the forum to see if I can get answers but with no joy.

Can somebody help me with my problem I am sure it is very simple to the experts ?

I have two tables ,

Table 1
which have the following fields:
project_code,
eng_1

Table 2
Which have the following fields:
project_code
eng_1

eng_1 will have a value of 0 or 1 in both tables

I need to list all the DISTINCT project codes from both tables but count the total from the eng_1 fields as seperate headings so the result would look something like this

project_code - table1.eng_1 - table2.eng_1

adhoc - 2 - 4
dev - 1 - 0
new - 0 - 1
help - 1 - 6

thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-01 : 08:16:25

try this


select project_code = isnull(t1.project_code, t2.project_code),
       eng_1 = count(t1.eng_1), 
       eng_2 = count(t2.eng_1) 
from   table1 t1 full outer join table t2
       on t1.project_code = t2.project_code
group by isnull(t1.project_code, t2.project_code)

pshearer
Starting Member

2 Posts

Posted - 2007-02-01 : 10:30:36

Thanks very much for your help.

I am nearly there, I have changed a few things to exactly fit in with what I have.

SELECT ISNULL(Jobs_Pending.Project_Code, Jobs_Completed.Project_Code) AS project_code, SUM(CAST(Jobs_Pending.Support_Eng2 AS Int)) AS Expr1,
SUM(CAST(Jobs_Completed.Support_Eng2 AS Int)) AS Expr2
FROM Jobs_Pending FULL OUTER JOIN
Jobs_Completed ON Jobs_Pending.Project_Code = Jobs_Completed.Project_Code
WHERE (Jobs_Pending.Project_Start_Date > '01/01/2007')
GROUP BY ISNULL(Jobs_Pending.Project_Code, Jobs_Completed.Project_Code)

I have change the Count to Sum(cast as the field is an nvarchar, but the only problem I have now is that it is not counting the support_eng2 fields correctly it seems to have the following results
which do not add up to the total in the fields but a really high number

Adhoc - 22345 - 54323

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources