Solved

Help with a SQL Query

Posted on 2016-11-01
23
46 Views
Last Modified: 2016-11-01
I have the following query

SELECT  DISTINCT Validate_Sections.USER_NAME, NGO_Details.DisplayName, CAST(
             CASE
                  WHEN COUNT(prerequisite_details.value) > 15
                     THEN 1
                  ELSE 0
             END AS int) as Prerequisites,Validate_Sections.SectionA_Val, Validate_Sections.SectionC_Val, Validate_Sections.SectionE_Val,
                         Validate_Sections.FileUpload_Val, USERS.RMEmail
FROM            Validate_Sections INNER JOIN
                         NGO_Details ON Validate_Sections.USER_NAME = NGO_Details.USER_NAME INNER JOIN
                         USERS ON Validate_Sections.USER_NAME = USERS.USER_NAME AND NGO_Details.USER_NAME = USERS.USER_NAME INNER JOIN
                         Prerequisite_Details ON USERS.USER_NAME = Prerequisite_Details.USER_NAME
                                     GROUP BY Validate_Sections.USER_NAME, NGO_Details.DisplayName,Validate_Sections.SectionA_Val, Validate_Sections.SectionC_Val, Validate_Sections.SectionD_Val, Validate_Sections.SectionE_Val,
                         Validate_Sections.FileUpload_Val, USERS.RMEmail
                                     ORDER BY NGO_Details.DisplayName

I would like to add another columns that is a summation of Prerequisites,SectionA_Val,SectionC_Val,SectionE_Val and FileUpload_Val columns.

Thanks,
Aditya
0
Comment
Question by:giveindia
  • 10
  • 10
  • 3
23 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41868195
Hi Aditya,

Try.. Group By column you can mention after partition by and the column whose sum you want you can mention in SUM(col..)

,SUM(Col_Names) OVER (PARTITION BY Prerequisites)
,SUM(Prerequisites) OVER (PARTITION BY colname)
,SUM(Col_Names) OVER (PARTITION BY Prerequisites)

,SUM(Col_Names) OVER PARTITION BY (Prerequisites,SectionA_Val,SectionC_Val,SectionE_Val,FileUpload_Val) Sums

--

SELECT  DISTINCT Validate_Sections.USER_NAME, NGO_Details.DisplayName, CAST(
             CASE 
                  WHEN COUNT(prerequisite_details.value) > 15
                     THEN 1 
                  ELSE 0 
             END AS int) as Prerequisites,Validate_Sections.SectionA_Val, Validate_Sections.SectionC_Val, Validate_Sections.SectionE_Val, 
                         Validate_Sections.FileUpload_Val, USERS.RMEmail
			,SUM(Col_Names) OVER PARTITION BY (Prerequisites,SectionA_Val,SectionC_Val,SectionE_Val,FileUpload_Val) Sums

FROM            Validate_Sections INNER JOIN
                         NGO_Details ON Validate_Sections.USER_NAME = NGO_Details.USER_NAME INNER JOIN
                         USERS ON Validate_Sections.USER_NAME = USERS.USER_NAME AND NGO_Details.USER_NAME = USERS.USER_NAME INNER JOIN
                         Prerequisite_Details ON USERS.USER_NAME = Prerequisite_Details.USER_NAME
                                     GROUP BY Validate_Sections.USER_NAME, NGO_Details.DisplayName,Validate_Sections.SectionA_Val, Validate_Sections.SectionC_Val, 
			Validate_Sections.SectionD_Val, Validate_Sections.SectionE_Val, 
                         Validate_Sections.FileUpload_Val, USERS.RMEmail
ORDER BY NGO_Details.DisplayName

--

Open in new window

0
 

Author Comment

by:giveindia
ID: 41868206
The query gives syntax errors. Could you please paste the entire query ?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41868211
You need sum of which columns and which columns you want in group by ? Please provide these

I shall give you the entire query..

May be...

SELECT  DISTINCT Validate_Sections.USER_NAME, NGO_Details.DisplayName, CAST(
             CASE
                  WHEN COUNT(prerequisite_details.value) > 15
                     THEN 1
                  ELSE 0
             END AS int) as Prerequisites,Validate_Sections.SectionA_Val, Validate_Sections.SectionC_Val, Validate_Sections.SectionE_Val,
                         Validate_Sections.FileUpload_Val, USERS.RMEmail
                        ,SUM(Prerequisites) OVER() Prerequisites
                        ,SUM(SectionA_Val) OVER() SectionA_Val
                        ,SUM(SectionC_Val) OVER() SectionC_Val
                        ,SUM(SectionE_Val) OVER() SectionE_Val
                        ,SUM(FileUpload_Val) OVER() FileUpload_Val
FROM            Validate_Sections INNER JOIN
                         NGO_Details ON Validate_Sections.USER_NAME = NGO_Details.USER_NAME INNER JOIN
                         USERS ON Validate_Sections.USER_NAME = USERS.USER_NAME AND NGO_Details.USER_NAME = USERS.USER_NAME INNER JOIN
                         Prerequisite_Details ON USERS.USER_NAME = Prerequisite_Details.USER_NAME
                                     GROUP BY Validate_Sections.USER_NAME, NGO_Details.DisplayName,Validate_Sections.SectionA_Val, Validate_Sections.SectionC_Val,
                  Validate_Sections.SectionD_Val, Validate_Sections.SectionE_Val,
                         Validate_Sections.FileUpload_Val, USERS.RMEmail
ORDER BY NGO_Details.DisplayName
0
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 

Author Comment

by:giveindia
ID: 41868212
Prerequisites,Validate_Sections.SectionA_Val, Validate_Sections.SectionC_Val, Validate_Sections.SectionE_Val,
                         Validate_Sections.FileUpload

The prerequisites is an alias column.

Thanks,
Aditya
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41868213
So you need sum of these column OK.

What about group by on columns ?

try..

SELECT  DISTINCT Validate_Sections.USER_NAME, NGO_Details.DisplayName, CAST(
             CASE 
                  WHEN COUNT(prerequisite_details.value) > 15
                     THEN 1 
                  ELSE 0 
             END AS int) as Prerequisites,Validate_Sections.SectionA_Val, Validate_Sections.SectionC_Val, Validate_Sections.SectionE_Val, 
                         Validate_Sections.FileUpload_Val, USERS.RMEmail
				,SUM(CAST(
             CASE 
                  WHEN COUNT(prerequisite_details.value) > 15
                     THEN 1 
                  ELSE 0 
             END AS int)) OVER() PrerequisitesSum
				,SUM(SectionA_Val) OVER() SectionA_Val
				,SUM(SectionC_Val) OVER() SectionC_Val
				,SUM(SectionE_Val) OVER() SectionE_Val
				,SUM(FileUpload_Val) OVER() FileUpload_Val
FROM            Validate_Sections INNER JOIN
                         NGO_Details ON Validate_Sections.USER_NAME = NGO_Details.USER_NAME INNER JOIN
                         USERS ON Validate_Sections.USER_NAME = USERS.USER_NAME AND NGO_Details.USER_NAME = USERS.USER_NAME INNER JOIN
                         Prerequisite_Details ON USERS.USER_NAME = Prerequisite_Details.USER_NAME
                                     GROUP BY Validate_Sections.USER_NAME, NGO_Details.DisplayName,Validate_Sections.SectionA_Val, Validate_Sections.SectionC_Val, 
			Validate_Sections.SectionD_Val, Validate_Sections.SectionE_Val, 
                         Validate_Sections.FileUpload_Val, USERS.RMEmail
ORDER BY NGO_Details.DisplayName

Open in new window

0
 

Author Comment

by:giveindia
ID: 41868227
It gives the following error.
Operand data type bit is invalid for sum operator.

Thanks,
Aditya
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41868229
Do you have any column of data type Bit out of below

Validate_Sections.SectionA_Val
, Validate_Sections.SectionC_Val
, Validate_Sections.SectionE_Val,
Validate_Sections.FileUpload

??
0
 

Author Comment

by:giveindia
ID: 41868231
All the columns other than the prerequisite column are bit columns.

Thanks,
Aditya
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41868236
Try..

SELECT  DISTINCT Validate_Sections.USER_NAME, NGO_Details.DisplayName, CAST(
             CASE 
                  WHEN COUNT(prerequisite_details.value) > 15
                     THEN 1 
                  ELSE 0 
             END AS int) as Prerequisites,Validate_Sections.SectionA_Val, Validate_Sections.SectionC_Val, Validate_Sections.SectionE_Val, 
                         Validate_Sections.FileUpload_Val, USERS.RMEmail
				,SUM(CAST(
             CASE 
                  WHEN COUNT(prerequisite_details.value) > 15
                     THEN 1 
                  ELSE 0 
             END AS int)) OVER() PrerequisitesSum
				,SUM(CAST(SectionA_Val AS INT)) OVER() SectionA_Val
				,SUM(CAST(SectionC_Val AS INT)) OVER() SectionC_Val
				,SUM(CAST(SectionE_Val AS INT)) OVER() SectionE_Val
				,SUM(CAST(FileUpload_Val AS INT)) OVER() FileUpload_Val
FROM            Validate_Sections INNER JOIN
                         NGO_Details ON Validate_Sections.USER_NAME = NGO_Details.USER_NAME INNER JOIN
                         USERS ON Validate_Sections.USER_NAME = USERS.USER_NAME AND NGO_Details.USER_NAME = USERS.USER_NAME INNER JOIN
                         Prerequisite_Details ON USERS.USER_NAME = Prerequisite_Details.USER_NAME
                                     GROUP BY Validate_Sections.USER_NAME, NGO_Details.DisplayName,Validate_Sections.SectionA_Val, Validate_Sections.SectionC_Val, 
			Validate_Sections.SectionD_Val, Validate_Sections.SectionE_Val, 
                         Validate_Sections.FileUpload_Val, USERS.RMEmail
ORDER BY NGO_Details.DisplayName

Open in new window

0
 

Author Comment

by:giveindia
ID: 41868241
What I meant was  if each of the columns has a value 1 the total should be 5 which is not the case here.

Thanks,
Aditya
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41868247
Ok, you are adding things to the requirement :)

 ..try..
may be this

--

SELECT  DISTINCT Validate_Sections.USER_NAME, NGO_Details.DisplayName, CAST(
             CASE 
                  WHEN COUNT(prerequisite_details.value) > 15
                     THEN 1 
                  ELSE 0 
             END AS int) as Prerequisites,Validate_Sections.SectionA_Val, Validate_Sections.SectionC_Val, Validate_Sections.SectionE_Val, 
                         Validate_Sections.FileUpload_Val, USERS.RMEmail
				,SUM(CAST(
             CASE 
                  WHEN COUNT(prerequisite_details.value) > 15
                     THEN 1 
                  ELSE 0 
             END AS int)) OVER() PrerequisitesSum
				,SUM(CASE WHEN SectionA_Val = 1 THEN 5 ELSE 0 END) OVER() SectionA_Val
				,SUM(CASE WHEN SectionC_Val = 1 THEN 5 ELSE 0 END) OVER() SectionC_Val
				,SUM(CASE WHEN SectionE_Val = 1 THEN 5 ELSE 0 END) OVER() SectionE_Val
				,SUM(CASE WHEN FileUpload_Val = 1 THEN 5 ELSE 0 END) OVER() FileUpload_Val
FROM            Validate_Sections INNER JOIN
                         NGO_Details ON Validate_Sections.USER_NAME = NGO_Details.USER_NAME INNER JOIN
                         USERS ON Validate_Sections.USER_NAME = USERS.USER_NAME AND NGO_Details.USER_NAME = USERS.USER_NAME INNER JOIN
                         Prerequisite_Details ON USERS.USER_NAME = Prerequisite_Details.USER_NAME
                                     GROUP BY Validate_Sections.USER_NAME, NGO_Details.DisplayName,Validate_Sections.SectionA_Val, Validate_Sections.SectionC_Val, 
			Validate_Sections.SectionD_Val, Validate_Sections.SectionE_Val, 
                         Validate_Sections.FileUpload_Val, USERS.RMEmail
ORDER BY NGO_Details.DisplayName



--

Open in new window

0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 41868251
Aditya, what your original query returns and what do you want to be returned?
Can you also provide Validate_Sections table schema?
0
 

Author Comment

by:giveindia
ID: 41868254
USER_NAME      nvarchar(255)      Unchecked
Validation_ID      uniqueidentifier      Unchecked
SectionA_Val      bit      Checked
SectionC_Val      bit      Checked
SectionD_Val      bit      Checked
SectionE_Val      bit      Checked
FileUpload_Val      bit      Checked
isdeleted      bit      Checked
created_by      nchar(10)      Checked
created_on      datetime      Checked
last_updated_by      nchar(10)      Checked
last_updated_on      datetime      Checked
            Unchecked
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41868255
Current Query output and the expected one ?
0
 

Author Comment

by:giveindia
ID: 41868257
Pawan Kumar Khowal the last query did not work.

Thanks,
Aditya
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41868258
I know but basically I dont know what is expected? :)
0
 

Author Comment

by:giveindia
ID: 41868260
Prerequisites      SectionA_Val      SectionC_Val      SectionE_Val      FileUpload_Val      PrerequisitesSum      SectionA_Val      SectionC_Val      SectionE_Val      FileUpload_Val
1      1      1      1      1      172      310      235      535      165

It should return 5 in this case which is the sum of all the column values.

Thanks,
Aditya
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41868266
Try..

SELECT  DISTINCT Validate_Sections.USER_NAME, NGO_Details.DisplayName, CAST(
             CASE 
                  WHEN COUNT(prerequisite_details.value) > 15
                     THEN 1 
                  ELSE 0 
             END AS int) as Prerequisites,Validate_Sections.SectionA_Val, Validate_Sections.SectionC_Val, Validate_Sections.SectionE_Val, 
                         Validate_Sections.FileUpload_Val, USERS.RMEmail
				,SUM(CAST(
             CASE 
                  WHEN COUNT(prerequisite_details.value) > 15
                     THEN 1 
                  ELSE 0 
             END AS int)) OVER() PrerequisitesSum
				,SUM(CASE WHEN SectionA_Val = 1 THEN 1 ELSE 0 END +
				CASE WHEN SectionC_Val = 1 THEN 1 ELSE 0 END + 
				CASE WHEN SectionE_Val = 1 THEN 1 ELSE 0 END + 				
				CASE WHEN FileUpload_Val = 1 THEN 1 ELSE 0 END) Sums
FROM            Validate_Sections INNER JOIN
                         NGO_Details ON Validate_Sections.USER_NAME = NGO_Details.USER_NAME INNER JOIN
                         USERS ON Validate_Sections.USER_NAME = USERS.USER_NAME AND NGO_Details.USER_NAME = USERS.USER_NAME INNER JOIN
                         Prerequisite_Details ON USERS.USER_NAME = Prerequisite_Details.USER_NAME
                                     GROUP BY Validate_Sections.USER_NAME, NGO_Details.DisplayName,Validate_Sections.SectionA_Val, Validate_Sections.SectionC_Val, 
			Validate_Sections.SectionD_Val, Validate_Sections.SectionE_Val, 
                         Validate_Sections.FileUpload_Val, USERS.RMEmail
ORDER BY NGO_Details.DisplayName

Open in new window

0
 
LVL 49

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41868267
Ok, try this:
SELECT  DISTINCT Validate_Sections.USER_NAME, NGO_Details.DisplayName, 
	CAST(CASE 
        	WHEN COUNT(prerequisite_details.value) > 15 THEN 1 
        	ELSE 0 
        	END AS int) as Prerequisites,
	Validate_Sections.SectionA_Val, Validate_Sections.SectionC_Val, Validate_Sections.SectionE_Val, Validate_Sections.FileUpload_Val, USERS.RMEmail,
	CAST(CASE 
		WHEN COUNT(prerequisite_details.value) > 15 THEN 1 
		ELSE 0 
	END AS int) + CAST(Validate_Sections.SectionA_Val AS INT) + CAST(Validate_Sections.SectionC_Val AS INT) + CAST(Validate_Sections.SectionE_Val AS INT)+ CAST(Validate_Sections.FileUpload_Val AS INT) AS TotalSum
 FROM Validate_Sections 
	INNER JOIN NGO_Details ON Validate_Sections.USER_NAME = NGO_Details.USER_NAME 
	INNER JOIN USERS ON Validate_Sections.USER_NAME = USERS.USER_NAME AND NGO_Details.USER_NAME = USERS.USER_NAME 
	INNER JOIN Prerequisite_Details ON USERS.USER_NAME = Prerequisite_Details.USER_NAME
GROUP BY Validate_Sections.USER_NAME, NGO_Details.DisplayName,Validate_Sections.SectionA_Val, Validate_Sections.SectionC_Val, Validate_Sections.SectionD_Val, Validate_Sections.SectionE_Val, Validate_Sections.FileUpload_Val, USERS.RMEmail
ORDER BY NGO_Details.DisplayName

Open in new window

0
 

Author Comment

by:giveindia
ID: 41868268
Still incorrect. The Sums is 140 instead of 5.

Thanks,
Aditya
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41868269
OR this

SELECT  DISTINCT Validate_Sections.USER_NAME, NGO_Details.DisplayName, CAST(
             CASE 
                  WHEN COUNT(prerequisite_details.value) > 15
                     THEN 1 
                  ELSE 0 
             END AS int) as Prerequisites,Validate_Sections.SectionA_Val, Validate_Sections.SectionC_Val, Validate_Sections.SectionE_Val, 
                         Validate_Sections.FileUpload_Val, USERS.RMEmail

				,CAST(
             CASE 
                  WHEN COUNT(prerequisite_details.value) > 15
                     THEN 1 
                  ELSE 0 
             END AS int)+				,
				CASE WHEN SectionA_Val = 1 THEN 1 ELSE 0 END +
				CASE WHEN SectionC_Val = 1 THEN 1 ELSE 0 END + 
				CASE WHEN SectionE_Val = 1 THEN 1 ELSE 0 END + 				
				CASE WHEN FileUpload_Val = 1 THEN 1 ELSE 0 END Sums

FROM            Validate_Sections INNER JOIN
                         NGO_Details ON Validate_Sections.USER_NAME = NGO_Details.USER_NAME INNER JOIN
                         USERS ON Validate_Sections.USER_NAME = USERS.USER_NAME AND NGO_Details.USER_NAME = USERS.USER_NAME INNER JOIN
                         Prerequisite_Details ON USERS.USER_NAME = Prerequisite_Details.USER_NAME
                                     GROUP BY Validate_Sections.USER_NAME, NGO_Details.DisplayName,Validate_Sections.SectionA_Val, Validate_Sections.SectionC_Val, 
			Validate_Sections.SectionD_Val, Validate_Sections.SectionE_Val, 
                         Validate_Sections.FileUpload_Val, USERS.RMEmail
ORDER BY NGO_Details.DisplayName

Open in new window

0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 41868270
Still incorrect. The Sums is 140 instead of 5.
Which one is incorrect?
1
 

Author Closing Comment

by:giveindia
ID: 41868272
Thanks Vitor.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
GeoClustering  and AOG 25 41
SQL Calculation syntax based on a parameter 2 30
Change this SQL to get all nodes 3 36
Need some alteration to below mention query 2 10
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

713 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