Need help with another simple SQL statement

Posted on 2008-11-04
Last Modified: 2012-05-05

I have another simple question about my sql statement.  I am pulling from 2 different tables into this one table.  They are joined by date.  The tblCountData.InCount is pulling information from a door counter which counts how many people walk through the doorway.  Before, there was only one record per day for the number of people.  So this formula worked fine.  But now, the door counter manufacturer recommends that I pull data for every hour.  When I pull the data in, I need to have only one entry per day.  How can I accomplish that?
SELECT tblCountData.RecDate, tblCountData.InCount, [cust per day].CountOfSlcustkey, [cust per day].Expr1

FROM tblCountData INNER JOIN [cust per day] ON tblCountData.RecDate=[cust per day].Expr1;

Open in new window

Question by:seanrhudy
    LVL 20

    Expert Comment

    by:chaitu chaitu
    by looking at above Q i didnt understand much so can you post the sample data and the desired result you want?
    LVL 19

    Accepted Solution

    SELECT tblCountData.RecDate, sum(tblCountData.InCount), [cust per day].sum(CountOfSlcustkey)
    FROM tblCountData INNER JOIN [cust per day] ON tblCountData.RecDate=[cust per day].Expr1;
    group by 1

    Author Comment

    Thanks, that worked.  One more quick question.  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;

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
    Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    734 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

    24 Experts available now in Live!

    Get 1:1 Help Now