SQL Select statement question

Posted on 2008-11-05
Last Modified: 2012-05-05
Hello, how can I subtract 23 from sum(tblCountData.Incount) only if the value is greater than 0?

SELECT tblCountData.RecDate, sum(tblCountData.Incount) AS trafficcount
FROM tblCountData
GROUP BY tblCountData.RecDate;
Question by:seanrhudy
    LVL 14

    Accepted Solution

    case when sum(tblCountData.Incount) > 0 then sum(tblCountData.Incount)- 23 Else sum(tblCountData.Incount) END AS trafficcount
    FROM tblCountData
    GROUP BY tblCountData.RecDate;

    Author Comment

    It says: syntax error (missing operator) in query expression

    Author Comment

    I'm using Access 2007
    LVL 14

    Expert Comment

    the query that posted for MS sql server, Sorry i can't help you in Access 2007

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now