How to fix the query so that it worked?

Question

SELECT 
    (SELECT 
         CREATEDBY, 
         COUNT(CUSTOMER) AS FIRSTMONTH 
     FROM 
         CUSTOMERSTABLE
     WHERE 
         CREATEDAT BETWEEN '2021-06-17' 
         AND '2021-07-17' 
         AND CREATEDBY IN ('Adam', 'John', 'Molly'
     GROUP BY 
         CREATEDBY), 
   (SELECT 
        CREATEDBY, 
        COUNT(CUSTOMER) AS SECONDMONTH 
    FROM 
        CUSTOMERSTABLE 
    WHERE 
        CREATEDAT BETWEEN '2021-07-17' 
        AND '2021-08-17' 
        AND CREATEDBY IN ('Adam', 'John', 'Molly'
    GROUP BY 
        CREATEDBY), 
   (SELECT 
        CREATEDBY, 
        COUNT(CUSTOMER) AS THIRDMONTH 
    FROM 
        CUSTOMERSTABLE
    WHERE 
        CREATEDAT BETWEEN '2021-08-17' 
        AND '2021-09-17' 
        AND CREATEDBY IN ('Adam', 'John', 'Molly'
    GROUP BY 
        CREATEDBY) 
FROM 
    CUSTOMERSTABLE

Errors:

Msg 116, Level 16, State 1, Line 24
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Msg 116, Level 16, State 1, Line 27
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Msg 116, Level 16, State 1, Line 30
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

@OlyOneil . . . What part of the error message do you not understand? It seems quite clear. Also, you need to tag with the database you are using. And to get a problem fixed, sample data, desired results, and a clear explanation of the code would all help. — Gordon Linoff, Sep 17 '21 at 12:14
Not sure what you are trying to do but looks like you just have 3 selects, break them apart and get rid of the FROM CustomersTable at the end. Or if you only want 1 result from each query, you need to limit it somehow in your logic then you can keep the 3 selects together, but still remove the last FROM — Brad, Sep 17 '21 at 12:14
I need to display 3 people and the number of records they created in 1st month, 2nd month, 3rd month — Olly O'neil, Sep 17 '21 at 12:18

score 1 · Answer 1 · answered Sep 17 '21 at 12:17

You can do conditional aggregation with single select statement :

SELECT CREATEDBY, 
       SUM(CASE WHEN CREATEDAT BETWEEN '2021-06-17' AND '2021-07-17' 
                THEN 1 ELSE 0 
           END) AS FIRSTMONTH,
       SUM(CASE WHEN CREATEDAT BETWEEN '2021-07-17' AND '2021-08-17' 
                THEN 1 ELSE 0 
           END) AS SECONDMONTH,
       SUM(CASE WHEN CREATEDAT BETWEEN '2021-08-17' AND '2021-09-17' 
                THEN 1 ELSE 0 
           END) AS THIRDMONTH        
FROM CUSTOMERSTABLE
WHERE CREATEDBY IN ('Adam', 'John', 'Molly')
GROUP BY CREATEDBY;

Aaron Bertrand · Accepted Answer · 2021-12-28T04:09:28.903

You could use a PIVOT, e.g.:

DECLARE @FirstDate  date = '20210617',
        @numMonths  tinyint = 3;

;WITH n (n) AS 
(
  SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n < @numMonths
),
d (MonthNumber, d) AS 
(
  SELECT n, DATEADD(MONTH, n.n-1, @FirstDate) FROM n
),
src AS
(
  SELECT c.CREATEDBY, MonthNumber, num = COUNT(*)
    FROM dbo.CustomerStable AS c INNER JOIN d 
      ON c.CREATEDAT >= d.d AND c.CREATEDAT < DATEADD(MONTH, 1, d.d)
    WHERE c.CREATEDBY IN ('Adam', 'John', 'Molly')
    GROUP BY c.CREATEDBY, d.MonthNumber
)
SELECT CREATEDBY,
       FIRSTMONTH  = COALESCE([1],0),
       SECONDMONTH = COALESCE([2],0),
       THIRDMONTH  = COALESCE([3],0)
FROM src
PIVOT (MAX(num) FOR MonthNumber IN ([1],[2],[3])) AS p;

Example db<>fiddle

Yes, it's a little more complicated than conditional aggregation, but it avoids hard-coding specific month boundaries into the query, and makes easier to change the number of months, the day that serves as the boundary, etc.

Also note that in your original query, if there were a row where CREATEDAT happened at midnight on the 17th (or time simply weren't recorded), it would be counted twice. This is why we stay away from BETWEEN for date range queries (see this post and this video).

How to fix the query so that it worked?

2 Answers2