[Last Call] Learn how to a build a cloud-first strategyRegister Now


Need help with another simple SQL statement

Posted on 2008-11-04
Medium Priority
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:Sean Rhudy
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22883375
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

NickUpson earned 500 total points
ID: 22884848
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

by:Sean Rhudy
ID: 22892058
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

831 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