How do I translate this query from MS Access into SQL Server

Question

How do I translate this into SQL Server:

select 
-Sum((12=[GroupNum])*[BoardStatus]) AS G12
FROM 
 QryAllBoardsByPacketIDs 
INNER JOIN 
 tBsReportGroups 
  ON QryAllBoardsByPacketIDs.NominalLengthMetric=tBsReportGroups.NomLenMet
GROUP BY 
 QryAllBoardsByPacketIDs.ParentPacketID, 
 QryAllBoardsByPacketIDs.GradeID, 
 QryAllBoardsByPacketIDs.NominalWidthMetric,   
 QryAllBoardsByPacketIDs.NominalThicknessMetric;

Kind regards,

Martin

Answer 1

Not quite sure I follow as I rarely use Access, but I believe this is what you're looking for:

SELECT -SUM(
   CASE WHEN 12=[GroupNum] THEN 1 ELSE 0 END
   * [BoardStatus]
   ) AS G12
[...]

CASE...WHEN is the inline "if" of T-SQL.

Answer 2

Cheers for that, why MS Access has to be different to everything else? Mmm. I got to the bottom of it like you said using :

G1 = SUM(CASE WHEN GroupNum = 1 THEN 1 ELSE 0 END)*BoardStatus,
G2 = SUM(CASE WHEN GroupNum = 2 THEN 1 ELSE 0 END)*BoardStatus,
.....
G10 = SUM(CASE WHEN GroupNum = 10 THEN 1 ELSE 0 END)*BoardStatus,
G11 = SUM(CASE WHEN GroupNum = 11 THEN 1 ELSE 0 END)*BoardStatus,
G12 = SUM(CASE WHEN GroupNum = 12 THEN 1 ELSE 0 END)*BoardStatus

It was the - before the sum that threw me, as the output is not negative. Regards,

Martin