Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 68
  • Last Modified:

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
0
giveindia
Asked:
giveindia
  • 10
  • 10
  • 3
1 Solution
 
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
 
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
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
 
Vitor MontalvãoMSSQL 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 10
  • 10
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now