Solved

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

Posted on 2010-08-18
14
234 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQl Query to find x consecutive Nbrs in a Table 30 97
Sql query with where clause 2 34
AJAX pass along a variable 3 46
SQL - format decimal in a string 5 39
In this article I will describe the Backup & Restore 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.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

839 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