Solved

SQL - Show grand total for group of records based on a date range without record detail

Posted on 2010-08-18
14
238 Views
Last Modified: 2012-05-10
I am trying to get a grand total of records for a date range without every record being displayed.  Here is what I have which does give the grand total but when I used the dates they all display.
<cfform  method="post" action="totals.cfm">
<cfinput type="text"  name="adid" required="yes" message="Please enter a adid number">
<!-- HERE WE FORMAT THE DATE PROPERLY TO INSERT INTO DATABASE USING A HIDDEN FIELD-->
<cfinput type="hidden" name="click_date" value="#DateFormat(now(), 'mm/dd/yyyy')#">
<cfinput type="submit"  name="Submit "value="Submit">

<p>Start Date: <br />

  <cfinput type="datefield" name="startDate" value="#DateFormat(Now()-7, 'mm/dd/yyyy')#" onFocus="this.value=''" style="background-color:##f2f2f2;">
  <br>
End Date: <br />
<cfinput type="datefield" name="endDate" value="#DateFormat(Now(), 'mm/dd/yyyy')#" onfocus="this.value=''" style="background-color:##f2f2f2;">
</cfform>
</td></tr>
</table>
<cfquery name="Total" datasource="hotbanana">
SELECT     COUNT(*) AS total_records, adid
FROM         tbl_click_data
GROUP BY adid
HAVING  adid = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.adid#">  

</cfquery>

<cfquery name="testresults" datasource="hotbanana">
    SELECT     COUNT(*) AS total_records, adid, click_date
FROM         tbl_click_data
GROUP BY adid, click_date
HAVING  adid = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.adid#"> 
   and (( tbl_click_data.click_date BETWEEN #CreateODBCDate(form.StartDate)# AND #CreateODBCDate(form.EndDate)#)) 
</cfquery>



<cfoutput query="Total">
  <tr><td>#Total.total_records#</td><td>#Total.adid#</td></tr>
  </cfoutput>

Open in new window

0
Comment
Question by:JohnMac328
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
  • +1
14 Comments
 
LVL 9

Expert Comment

by:pauloaguia
ID: 33464305
Maybe because all the dates are different (have different time part)?

If that's the case and you want to count all records in the same day, then you could try a query like

SELECT     COUNT(*) AS total_records, adid, CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()))) AS click_date
FROM         tbl_click_data
GROUP BY adid, CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))
0
 

Author Comment

by:JohnMac328
ID: 33464376
It gave this error

Each GROUP BY expression must contain at least one column that is not an outer reference
0
 
LVL 1

Expert Comment

by:BenHarpas
ID: 33464599
I'm not sure if i understood the question but here it goes:

SELECT     COUNT(*) as total_records  FROM (SELECT adid, click_date
FROM         tbl_click_data
GROUP BY adid, click_date
HAVING  adid = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.adid#">
   and (( tbl_click_data.click_date BETWEEN #CreateODBCDate(form.StartDate)# AND #CreateODBCDate(form.EndDate)#)))
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:JohnMac328
ID: 33464653
It has a problem with a ) on this line

   and (( tbl_click_data.click_date BETWEEN #CreateODBCDate(form.StartDate)# AND #CreateODBCDate(form.EndDate)#)))

I tried adding and removing one but it still didn't like it.
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33464718
You will need to alias the derived Query like below

and (( tbl_click_data.click_date BETWEEN #CreateODBCDate(form.StartDate)# AND #CreateODBCDate(form.EndDate)#)))Derived
0
 
LVL 1

Expert Comment

by:BenHarpas
ID: 33464741
maybe this solve solve it


SELECT     COUNT(*) as total_records  FROM
(SELECT adid, click_date
FROM         tbl_click_data
GROUP BY adid, click_date
HAVING  adid = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.adid#">
   and (( tbl_click_data.click_date BETWEEN #CreateODBCDate(form.StartDate)# AND #CreateODBCDate(form.EndDate)#))) subquery
0
 

Author Comment

by:JohnMac328
ID: 33464774
Ben and vdr1620, both of yours gave the same error

Element ADID is undefined in TESTRESULTS. for this line

<tr><td>#testresults.total_records#</td><td>#testresults.adid#</td></tr>
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33464825
Of course because the string is not in Single Quotes.. I missed it in my previous post

HAVING  adid = ''
   and (( tbl_click_data.click_date BETWEEN #CreateODBCDate(form.StartDate)# AND #CreateODBCDate(form.EndDate)#)))
0
 
LVL 1

Expert Comment

by:BenHarpas
ID: 33464846

SELECT     COUNT(*) as total_records, subquery.adid as adid  FROM
(SELECT adid, click_date
FROM         tbl_click_data
GROUP BY adid, click_date
HAVING  adid = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.adid#">
   and (( tbl_click_data.click_date BETWEEN #CreateODBCDate(form.StartDate)# AND #CreateODBCDate(form.EndDate)#))) subquery
group by subquery.adid
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33464854
sorry, Ignore my previous post ---- " Of course because the string ................."
0
 
LVL 1

Expert Comment

by:BenHarpas
ID: 33464863
ups...  

Like This.

SELECT     COUNT(*) as total_records, subquery.adid as adid  FROM
(SELECT adid, click_date
FROM         tbl_click_data
GROUP BY adid, click_date
HAVING  adid = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.adid#">
   and (( tbl_click_data.click_date BETWEEN #CreateODBCDate(form.StartDate)# AND #CreateODBCDate(form.EndDate)#))) subquery
group by adid
0
 

Author Comment

by:JohnMac328
ID: 33464899
ydr1620  no offense taken but it did give a error.

Ben I did not try your last one because the previous one worked fine, what did you change?
0
 
LVL 1

Accepted Solution

by:
BenHarpas earned 500 total points
ID: 33465177
John,
i made a subquery with alias name "subquery", that returns fields adid and click_date.
So, in Select, i selected field subquery.adid, because it's the alias for the subquery and field name.

In the end, i grouped subquery.adid so you don't have multiple lines for each line that subquery returns.

Hope it helps with my poor english ;)

Happy coding
0
 

Author Closing Comment

by:JohnMac328
ID: 33465199
Your English is fine, thanks for your help.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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 video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

724 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