Solved

Help with a SQL Query

Posted on 2016-11-01
23
27 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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
Comment Utility
The query gives syntax errors. Could you please paste the entire query ?
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
 

Author Comment

by:giveindia
Comment Utility
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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
Comment Utility
It gives the following error.
Operand data type bit is invalid for sum operator.

Thanks,
Aditya
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
Comment Utility
All the columns other than the prerequisite column are bit columns.

Thanks,
Aditya
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
Comment Utility
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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Current Query output and the expected one ?
0
 

Author Comment

by:giveindia
Comment Utility
Pawan Kumar Khowal the last query did not work.

Thanks,
Aditya
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
I know but basically I dont know what is expected? :)
0
 

Author Comment

by:giveindia
Comment Utility
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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
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
Comment Utility
Still incorrect. The Sums is 140 instead of 5.

Thanks,
Aditya
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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 45

Expert Comment

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

Author Closing Comment

by:giveindia
Comment Utility
Thanks Vitor.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

744 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now