Only one expression can be specified in the select list when the subquery is not introduced with EXISTS with multiple queries

Question

1.00/5 (1 vote)

See more:

When executing the below query i am getting the error but i need all the columns in the result

SQL

SELECT
    (
        select distinct
            ID,
            [Name],
            SUM(fee)
        from table1
        where date1 >= '2021-01-01'
              and date2 < '2021-02-01'
        group by ID,
                 [Name]
    ) as Count1,
    (
        select distinct
            ID,
            [Name],
            SUM(fee)
        from table2
        where date1 >= '2021-01-01'
              and date2 < '2021-02-01'
        group by ID,
                 [Name]
    ) as Count2,
    (
        select distinct
            ID,
            [Name],
            COUNT(StudentFee)
        from table3
        where ISDATE(InRange) = 1
              and Paid
              between '2021-01-01' and '2021-02-01'
    ) AS Count3,
    (
        select distinct
            ID,
            [Name],
            COUNT(StudentFee)
        from table3
        where InRange IS NOT NULL
              and (
                      Paid = 1
                      AND DatePaid IS NOT NULL
                  )
              and Paid
              between '2021-01-01' and '2021-02-01'
    ) AS Count4

What I have tried:

            Count1	Count2	Count3	Count4
Student1	10000	7000	4	    2
Student2	40000	10000	3	    5
Student3	70000	6000	4	    3       
Student4	80000	4000	2	    1
Student5	5000	9000	1	    4

Posted 19-Apr-23 7:00am

Nickname 2023

Updated 17-May-23 3:08am

v7

Add a Solution

Comments

Member 15627495 20-Apr-23 2:16am

great help by the 'expected output',

start the main query by :

 select distinct ID , name , ( ....  // then all the sub queries to apply  'maths' )

with the Main query, you build all column results ready for the 'output'.

What is the error feedback you have ?

Richard Deeming 15-May-23 4:19am

Removing the content of your question after it has been answered is extremely rude.

I have rolled back your destructive edit.

Richard Deeming 17-May-23 10:39am

Once again, for those in the back:

Removing the content of your question after it has been answered is extremely rude.

I have rolled back your destructive edit. Do it again, and you'll get reported as a troll.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2023-04-19T21:51:00

This is the sort of problem you encounter when your data is not properly normalized. You have the "student" data replicated across three separate tables, rather than having one table for students and related tables for the associated records.

And what's up with those table names? Calling your tables table1, table2, etc. is a terrible idea. How is anyone looking at your code or your database supposed to understand what data is stored where? And yes, that includes you when you come back to this code in a month's time. Give your tables meaningful names to reflect the data they contain.

As a hacky workaround, try something like this:

SQL

WITH cteStudents As
(
    SELECT ID, [Name] FROM table1
    UNION SELECT ID, [Name] FROM table2
    UNION SELECT ID, [Name] FROM table3
)
SELECT
    S.ID,
    S.[Name],
    (
        SELECT Sum(fee) 
        FROM table1 As T 
        WHERE T.ID = S.ID 
        And T.[Name] = S.[Name] 
        And T.date1 >= '2021-01-01' 
        And T.date2 < '2021-02-01'
    ) As Count1,
    (
        SELECT Sum(fee) 
        FROM table2 As T 
        WHERE T.ID = S.ID 
        And T.[Name] = S.[Name] 
        And T.date1 >= '2021-01-01' 
        And T.date2 < '2021-02-01'
    ) As Count2,
    (
        SELECT COUNT(StudentFee) 
        FROM table3 As T 
        WHERE T.ID = S.ID 
        And T.[Name] = S.[Name] 
        And ISDATE(T.InRange) = 1
        And T.Paid >= '2021-01-01' 
        And T.Paid < '2021-02-01'
    ) As Count3,
    (
        SELECT COUNT(StudentFee) 
        FROM table3 As T 
        WHERE T.ID = S.ID 
        And T.[Name] = S.[Name] 
        And T.InRange Is Not Null
        And T.Paid >= '2021-01-01' 
        And T.Paid < '2021-02-01'
        -- NB: "Paid" cannot be both a date and equal to 1 at the same time!
        --     You need to explain the precise structure of your tables,
        --     and your exact requirements for this count.
    ) As Count4
FROM
    cteStudents As S
;