Solved

Include DISTINCT selection

Posted on 2012-04-10
10
187 Views
Last Modified: 2012-04-12
I have the following sql string:
SELECT tblJJDP.ID,Max(LTRIM(RTRIM(Nu)))Nu, Sum(Total)TotalsperStudent   FROM AttendanceGrid INNER JOIN tblJJDP ON AttendanceGrid.[ID]=tblJJDP.[ID] Where tblJJDP.[CSW/TitleXX] = 'I' And Combo6 = 'Alternatives' And [Month]= 'January' Group by tblJJDP.ID

I want to modify it so that I only select DISTINCT Activity from my table called Attendance Grid.

How can I do that?
0
Comment
Question by:al4629740
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 7

Accepted Solution

by:
Cboudroz earned 333 total points
ID: 37828928
SELECT 
	tblJJDP.ID
	,Max(LTRIM(RTRIM(Nu)))Nu
	, Sum(Total)TotalsperStudent   
FROM 
	(
		SELECT DISTINCT
			*
		FROM 
			AttendanceGrid 
	) AS A
	INNER JOIN tblJJDP 
		ON A.[ID]=tblJJDP.[ID] 
WHERE 
	tblJJDP.[CSW/TitleXX] = 'I' 
	And Combo6 = 'Alternatives' 
	And [Month]= 'January' 
Group by 
	tblJJDP.ID

Open in new window

0
 
LVL 14

Assisted Solution

by:nishant joshi
nishant joshi earned 167 total points
ID: 37828958
check delow code you will get distinct activity from attendancegrid


SELECT a.id,a.Activity 
       MAX(Ltrim(Rtrim(nu))) as nu, 
       SUM(total)   as totalsperstudent 
FROM   attendancegrid a
INNER JOIN tbljjdp  t
ON a.[ID] = t.[ID] 
WHERE  t.[CSW/TitleXX] = 'I' 
       AND combo6 = 'Alternatives' 
       AND [Month] = 'January' 
GROUP  BY a.Activity,a.id  

Open in new window


Regards,
nishant
0
 

Author Comment

by:al4629740
ID: 37829436
This is what I have.  Its NOT working

SELECT tblJJDP.ID,Max(LTRIM(RTRIM(Nu)))Nu, Sum(Total)TotalsperStudent   FROM (SELECT DISTINCT Activity FROM AttendanceGrid ) as a INNER JOIN tblJJDP ON AttendanceGrid.[ID]=tblJJDP.[ID] Where tblJJDP.[CSW/TitleXX] = 'I' And Combo6 = 'Alternatives' And [Month]= 'January' Group by tblJJDP.ID
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 7

Expert Comment

by:Cboudroz
ID: 37829456
error message?
0
 

Author Comment

by:al4629740
ID: 37830103
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "AttendanceGrid.ID" could not be bound.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Month'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Nu'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Total'.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37830696
This is what I have.  Its NOT working
Is there any reason you cannot use Cboudroz solution posted here: http:#a37828928
0
 
LVL 14

Expert Comment

by:nishant joshi
ID: 37830928
SELECT a.id,a.Activity 
       MAX(Ltrim(Rtrim(a.nu))) as nu, 
       SUM(a.total)   as totalsperstudent 
FROM   attendancegrid a
INNER JOIN tbljjdp  t
ON a.[ID] = t.[ID] 
WHERE  t.[CSW/TitleXX] = 'I' 
       AND a.combo6 = 'Alternatives' 
       ANDa.[Month] = 'January' 
GROUP  BY a.Activity,a.id  

Open in new window

0
 

Author Comment

by:al4629740
ID: 37831083
Sorry, I was confused a bit.  what are the letters like "a" and "t"

are they just abbreviations not meant to be typed that way in actual code?
0
 
LVL 14

Expert Comment

by:nishant joshi
ID: 37831171
a which is an alias of attendancegrid table and t which is an alias of tbljjdp .
0
 
LVL 7

Assisted Solution

by:Cboudroz
Cboudroz earned 333 total points
ID: 37832750
-->Is there any reason you cannot use Cboudroz solution posted here: http:#a37828928

You change the select distinct "*" by "Activity", so now the other column in AttendanceGrid are not return anymore : (ID,Month, NU, Total)

	(
		SELECT DISTINCT
			*
		FROM 
			AttendanceGrid 
	) AS A

Open in new window


	(
		SELECT DISTINCT
			Activity
		FROM 
			AttendanceGrid 
	) AS A

Open in new window

0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question