Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
3  
I think you need to explain what this part does (12=[GroupNum])*[BoardStatus]. It looks like you are summing all rows where GroupNum is 12. If that is a case then you could use sum(BoardStatus) and add a where clause where GroupNum = 12. –  Mikael Eriksson Jun 18 at 5:21

2 Answers 2

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.

share|improve this answer
    
I would like to ask if the -SUM has any translation –  user1124937 Jun 19 at 6:38
1  
@user1124937: Try replacing -sum((12=[GroupNum])* with sum(case when GroupNum = 12 then 1 else 0 end * –  Pieter Geerkens Oct 2 at 22:22

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.