Solved

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

Posted on 2010-08-18
14
231 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
  • 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
 

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

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

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

13 Experts available now in Live!

Get 1:1 Help Now