Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-08-18
14
Medium Priority
?
243 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 2000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

610 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