Help with a SQL Query

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
giveindiaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
giveindiaAuthor Commented:
The query gives syntax errors. Could you please paste the entire query ?
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
Pawan KumarDatabase ExpertCommented:
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
 
giveindiaAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
giveindiaAuthor Commented:
It gives the following error.
Operand data type bit is invalid for sum operator.

Thanks,
Aditya
0
 
Pawan KumarDatabase ExpertCommented:
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
 
giveindiaAuthor Commented:
All the columns other than the prerequisite column are bit columns.

Thanks,
Aditya
0
 
Pawan KumarDatabase ExpertCommented:
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
 
giveindiaAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Aditya, what your original query returns and what do you want to be returned?
Can you also provide Validate_Sections table schema?
0
 
giveindiaAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
Current Query output and the expected one ?
0
 
giveindiaAuthor Commented:
Pawan Kumar Khowal the last query did not work.

Thanks,
Aditya
0
 
Pawan KumarDatabase ExpertCommented:
I know but basically I dont know what is expected? :)
0
 
giveindiaAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
giveindiaAuthor Commented:
Still incorrect. The Sums is 140 instead of 5.

Thanks,
Aditya
0
 
Pawan KumarDatabase ExpertCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Still incorrect. The Sums is 140 instead of 5.
Which one is incorrect?
1
 
giveindiaAuthor Commented:
Thanks Vitor.
0
All Courses

From novice to tech pro — start learning today.