asked on

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

Pawan Kumar

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

giveindia

ASKER

The query gives syntax errors. Could you please paste the entire query ?

Pawan Kumar

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

giveindia

ASKER

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

The prerequisites is an alias column.

Thanks,
Aditya

Pawan Kumar

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

giveindia

ASKER

It gives the following error.
Operand data type bit is invalid for sum operator.

Thanks,
Aditya

Pawan Kumar

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

??

giveindia

ASKER

All the columns other than the prerequisite column are bit columns.

Thanks,
Aditya

Pawan Kumar

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

giveindia

ASKER

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

Pawan Kumar

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

Vitor Montalvão

Aditya, what your original query returns and what do you want to be returned?
Can you also provide Validate_Sections table schema?

giveindia

ASKER

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

Pawan Kumar

Current Query output and the expected one ?

giveindia

ASKER

Pawan Kumar Khowal the last query did not work.

Thanks,
Aditya

Pawan Kumar

I know but basically I dont know what is expected? :)

giveindia

ASKER

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

Pawan Kumar

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

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

giveindia

ASKER

Still incorrect. The Sums is 140 instead of 5.

Thanks,
Aditya

Pawan Kumar

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

Vitor Montalvão

Still incorrect. The Sums is 140 instead of 5.

Which one is incorrect?

giveindia

ASKER

Thanks Vitor.