How to solve this error ' only one expression can be specified in the select list when the subquery is not introduced with EXISTS. ' In SQL server

Question

1.00/5 (1 vote)

See more:

In the query i am trying to achieve a count particularly for each date but how do i Achieve it?

What I have tried:

select [dbo].[GetCheckInCheckOut](11867 , format (Cast(StartDateTime as Date),'yyyy-MM-dd'))Presence ,	UserID ,Cast(StartDateTime as Date)as StartDateTime,(select VendorId,count(*) from tblVisits where CheckInDateTime >= '2022-02-01' and CheckOutDateTime <= '2022-02-25'  and CreatedBy=11867 group by VendorID)VisitCount from tblAttendance as a   
left join tblVisits as v on a.UserID =v.CreatedBy
where 
StartDateTime between '2022-02-01' and  '2022-02-25' 

and
UserID =11867 Group by UserID ,Cast(StartDateTime as Date)

Posted 24-Feb-22 17:23pm

Preet Vakharia

Updated 24-Feb-22 20:06pm

Add a Solution

2 solutions

Add a Solution

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

Dave Kreskowiak · Answer 1 · 2022-02-24T18:58:00

It would help if you reformatted your SQL so it's readable, and capitalize SQL keywords:

SQL

SELECT [dbo].[GetCheckInCheckOut](11867    <--- syntax error
    , FORMAT(CAST(StartDateTime AS Date), 'yyyy-MM-dd'))Presence  <--- syntax error
    , UserID
    , CAST(StartDateTime AS Date) AS StartDateTime
    , (
        SELECT VendorId
             , COUNT(*) 
        FROM tblVisits 
        WHERE CheckInDateTime >= '2022-02-01' 
          AND CheckOutDateTime <= '2022-02-25'
          AND CreatedBy = 11867
          GROUP BY VendorID
       )VisitCount FROM tblAttendance AS a         <--- and yet another syntax error
LEFT JOIN tblVisits AS v 
ON a.UserID = v.CreatedBy
WHERE StartDateTime BETWEEN '2022-02-01' AND '2022-02-25' 
  AND UserID = 11867 
GROUP BY UserID, CAST(StartDateTime AS Date)

I have no idea what you're trying to do, but you've got some cleaning up to do just to make your SQL readable, and therefore debuggable.

Maciej Los · Answer 2 · 2022-02-24T20:06:00

As Dave already mentioned, the format of your SQL code is not readible and thus contains several errors.

The error message is quite obvious. Your subquery returns more then one column. See:
How to Fix “Only one expression can be specified in the select list…” in SQL Server[^]

Now, take a look at your sql code:

SQL

select [dbo].[GetCheckInCheckOut](11867 , format (Cast(StartDateTime as Date),'yyyy-MM-dd'))Presence ,	
    UserID ,
    Cast(StartDateTime as Date) as StartDateTime,
    (
      select VendorId,count(*)
      from tblVisits
      where CheckInDateTime >= '2022-02-01' and CheckOutDateTime <= '2022-02-25'  and 
          CreatedBy=11867
      group by VendorID
   ) VisitCount  -- <-- this subquery returns more than 1 column: VendorId And the result of COUNT(*)
from tblAttendance as a   
left join tblVisits as v on a.UserID =v.CreatedBy
where StartDateTime between '2022-02-01' and  '2022-02-25' and
UserID =11867 Group by UserID, Cast(StartDateTime as Date
)

On the other hand, you should start learning about variables[^]. In your query the value of 11867 is entered 3 times: at the beginning, in the middle and at the end. Replace it with variable!

I also strongly recommend to rethink your query. Start with smaller pieces. When you get them working, try to connect them each other.

You can share example data at: db<>fiddle[^], then copy the link and paste it into your question. Then, we'll be able to inspect your sql code.

How to solve this error ' only one expression can be specified in the select list when the subquery is not introduced with EXISTS. ' In SQL server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How to solve this error ' only one expression can be specified in the select list when the subquery is not introduced with EXISTS. ' In SQL server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us