Can get the Union to work correctly

Question

   Select CorpID,
    Convert(VarChar(2),Month(E.BeginDate)) + '/' + Convert(VarChar(4),Year(E.BeginDate)),
    Count(Year(e.BeginDate)) As 'total Screen'
    --Count(Month(E.CurrentBeginDate))
    From dbo.NonCalls E
    Where E.BeginDate between {d'2013-01-01'} and {d'2013-12-31'}
    Group By CorpID, Year(E.BeginDate),Month(E.BeginDate)

    Union ALL


    Select CorpID,
    Convert(VarChar(2),Month(E.CurrentBeginDate)) + '/' + Convert(VarChar(4),Year(E.CurrentBeginDate)),
    Count(Year(e.CurrentBeginDate)) As 'total Screen'
    --Count(Month(E.CurrentBeginDate))
    From dbo.Employee E
    Where E.CurrentBeginDate between {d'2013-01-01'} and {d'2013-12-31'}
    Group By CorpID, Year(E.CurrentBeginDate),Month(E.CurrentBeginDate)
    --Order By CorpID, Year(E.CurrentBeginDate), Month(E.CurrentBeginDate)

I change my code to this and now I get the numbers that i was looking for the only problem is that it's not sorted i need it to sorted by Corpid and then by date 01-02-03 etc I'm not quite sure

how to get that accomplish any help would be greatly apreciated.

Msg 205, Level 16, State 1, Line 1 All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. the database is SQL — user3571281, Apr 25 '14 at 04:03
@user3571281 - "SQL" isn't the db, it's a standardized language that multiple dbs implement. It's like going into the grocery store and looking at ice cream - Dreyer's has vanilla, as does Costco, Haagen Daz, Ben & Jerry's, etc. Oh, and they're all subtly different. Don't include commented-out code - it's not supposed to be run and can just confuse things, so does not server a benefit. (Also does eliminating it from your query cause it to run?). Your query does not appear to be invalid in a way that would throw the given error. — Clockwork-Muse, Apr 25 '14 at 04:35

Allan S. Hansen · Answer 1 · 2014-04-25T06:19:09.157

You have 2 CurrentBeginDate in your second part of the UNION causing that one to return 5 columns, but the first part only 4 columns

SELECT    
 CorpID ,
 CurrentBeginDate <--HERE,
 CONVERT(VARCHAR(2), MONTH(E.CurrentBeginDate)) + '/'
           + CONVERT(VARCHAR(4), YEAR(E.CurrentBeginDate)) AS CurrentBeginDate <--HERE,
 COUNT(YEAR(e.CurrentBeginDate)) AS 'total Screen' ,
 '' AS d1

As the error message says, for the union to work, it need the same amount of columns returned from all parts of the query.

score 0 · Answer 2 · edited May 23 '17 at 10:26

As an answer to your "new question about sorting the now correctly unioned data:

You need to treat the unioned resultset as a derived table and select from it with your order. by.

SELECT * 
  FROM (<your unioned query)
 ORDER BY CorpID
      ,Year(CurrentBeginDate)
      ,Month(CurrentBeginDate)

See here for more more complete discussion: TSQL ORDER-BY with a UNION of disparate datasets

Can get the Union to work correctly

2 Answers2