With ROLLUP not accurately summing up rows?

Dev Shed Forums Sponsor:

November 1st, 2009, 03:18 PM

mwaterous

Registered User

Join Date: Nov 2009

Posts: 4

Time spent in forums: 38 m 17 sec
Reputation Power: 0

With ROLLUP not accurately summing up rows?

I might've missed something in the explanation on MySQL's documentation as to the proper usage/operation of WITH ROLLUP. My impression is that it would provide me with a summary row, that would provide totals for data generated from a SELECT statement.

Here's the Query:

mysql Code:

Original - mysql Code
SELECT SUBSTR( timestamp, 1, 10 ) AS timestamp, COUNT( DISTINCT ip ) AS visitors, COUNT( timestamp ) AS pageviews FROM `%s` WHERE feed = '' AND spider = '' AND preserved IS NULL GROUP BY SUBSTR( timestamp, 1, 10 ) WITH ROLLUP


SELECT SUBSTR( timestamp, 1, 10 ) AS timestamp, COUNT( DISTINCT ip ) AS visitors, COUNT( timestamp ) AS pageviews
FROM `%s`
WHERE feed = '' AND spider = '' AND preserved IS NULL
GROUP BY SUBSTR( timestamp, 1, 10 )
WITH ROLLUP

And the results are obviously not accurate totals:

Code:

2009-10-31   |   8   |   14
2009-11-01   |  72   |  149
NULL         |  78   |  163

72 + 8 = 78? Maybe according to my high school math teacher, but I've learned since that 2 + 8 = 10, which means...

November 1st, 2009, 03:31 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 20,768

Time spent in forums: 2 Months 1 Week 3 Days 17 h 39 m 41 sec
Reputation Power: 2481

try it with COUNT instead of COUNT DISTINCT and see if that gives you a hint

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

November 1st, 2009, 04:01 PM

mwaterous

Registered User

Join Date: Nov 2009

Posts: 4

Time spent in forums: 38 m 17 sec
Reputation Power: 0

The problem is that I need the DISTINCT clause for the query to return the results I'm looking for. Without it, the two columns return duplicate results.

If there's a way to get the same type of result that simply adds the total of all columns with no other bias than straight math, I'd be interested in knowing.

November 1st, 2009, 04:42 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 20,768

Time spent in forums: 2 Months 1 Week 3 Days 17 h 39 m 41 sec
Reputation Power: 2481

Quote:

Originally Posted by mwaterous

The problem is that I need the DISTINCT clause for the query to return the results I'm looking for.

that's what it's giving you -- COUNT DISTINCT on all results

November 1st, 2009, 05:29 PM

mwaterous

Registered User

Join Date: Nov 2009

Posts: 4

Time spent in forums: 38 m 17 sec
Reputation Power: 0

Quote:

Originally Posted by r937

that's what it's giving you -- COUNT DISTINCT on all results

Ohhh, wait I get what you mean. So two of the IPs returned from the second day also exist on the first day, so the total of the distinct from each day is actually only 78.

So returns to my original question... is there a version of ROLLUP I could use that would instead return the sum of all columns unbiased by the SELECT?

November 1st, 2009, 05:43 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 20,768

Time spent in forums: 2 Months 1 Week 3 Days 17 h 39 m 41 sec
Reputation Power: 2481

sorry, there is only one version of WITH ROLLUP, and the options are: WITH ROLLUP

November 2nd, 2009, 04:39 AM

pabloj

Modding: Oracle MsSQL Firebird

Dev Shed God 7th Plane (8000 - 8499 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 8,388

Time spent in forums: 3 Months 4 Days 22 h 50 sec
Reputation Power: 532

Wrap it into another select and apply the rollup to the external one.
Something like

sql Code:

Original - sql Code
select aa.time_stamp sum(aa.visitors) total_visitors, sum(aa.pageviews) total_pageviews from ( SELECT SUBSTR( timestamp, 1, 10 ) AS time_stamp, COUNT( DISTINCT ip ) AS visitors, COUNT( timestamp ) AS pageviews FROM `%s` WHERE feed = '' AND spider = '' AND preserved IS NULL GROUP BY SUBSTR( timestamp, 1, 10 ) ) aa group by aa.time_stamp WITH ROLLUP

 
SELECT 
aa.time_stamp SUM(aa.visitors) total_visitors, SUM(aa.pageviews) total_pageviews
FROM
(
SELECT 
SUBSTR( timestamp, 1, 10 ) AS time_stamp, 
COUNT( DISTINCT ip ) AS visitors, 
COUNT( timestamp ) AS pageviews
FROM 
`%s`
WHERE 
feed = '' 
AND spider = '' 
AND preserved IS NULL
GROUP BY 
SUBSTR( timestamp, 1, 10 )
) aa GROUP BY aa.time_stamp
WITH ROLLUP

__________________
My blog
Tutorials about OSS databases, DBMonster ...
Contribute to OSS Development, fill bug reports!
Developer Shed eSupport
Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK)
An introduction to database normalization
Natural or Surrogate key
Custom ordering for your results
Correlated and uncorrelated subqueries
Don't turn your outer joins into inner joins
Random data (with a bias)

November 2nd, 2009, 02:34 PM

mwaterous

Registered User

Join Date: Nov 2009

Posts: 4

Time spent in forums: 38 m 17 sec
Reputation Power: 0

Quote:

Originally Posted by pabloj

Wrap it into another select and apply the rollup to the external one.

That worked great, thanks!

Obviously it's invisible to the naked eye, but does anybody have a good summary on the overhead of wrapping a SELECT inside of a SELECT? Is it negligible or due to the fact that I have to loop through each row anyways is it faster to just stick with counting it inside of PHP?

I realize that's probably not as simple as yes or no, given possible factors, but I've only just recently started learning how to write more complex SQL queries to try and reduce the workload on my actual PHP scripts... so I wonder here and there whether I'm actually optimizing things or just placing more load on the SQL server haha... :P

November 3rd, 2009, 04:10 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 8,388

Time spent in forums: 3 Months 4 Days 22 h 50 sec
Reputation Power: 532

Start by looking at the explain plan for that query.
You might want to check this blog post as far as performance of "with rollup" goes.

Viewing: Dev Shed Forums > Databases > MySQL Help > With ROLLUP not accurately summing up rows?

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.

Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.

Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.

Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.

Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.

More Free IT White Papers!