Sum all sums

3 Message(s) by 2 Author(s) originally posted in mysql discussion

From: dennis.sprengers

Date: Saturday, October 27, 2007

Consider the following table

<database> A collection of {records} in a {relational database}.

	(1997-06-04)

named

<networking> Name Daemon.

	<networking> A {Unix} {background} process that converts
	{hostnames} to {Internet addresses} for the {TCP/IP}
	{protocol}.

	{Unix manual page}: named(8).

	See also {DNS}.

	(1995-03-28)

"stats_dt". It stores an hourly
sum

1. <theory> In {domain theory}, the sum A + B of two {domains}
	contains all elements of both domains, modified to indicate
	which part of the union they come from, plus a new {bottom}
	element.  There are two constructor functions associated with
	the sum:

		inA : A -> A+B       inB : B -> A+B
		inA(a) = (0,a)	     inB(b) = (1,b)

	and a disassembly operation:

		case d of {isA(x) -> E1; isB(x) -> E2}

	This can be generalised to arbitrary numbers of domains.

	See also {smash sum}, {disjoint union}.

	2. <tool> A {Unix} utility to calculate a 16-bit {checksum} of
	the data in a file.  It also displays the size of the file,
	either in {kilobytes} or in 512-byte blocks.  The checksum may
	differ on machines with 16-bit and 32-bit ints.

	{Unix manual page}: sum(1).

	(1995-03-16)

mary of hits and sessions on the server:

+------------+------+----------+------+
| date

<convention, data> A string unique to a time duration of 24
	hours between 2 successive midnights defined by the local time
	zone.  The specific representation of a date will depend on
	which calendar convention is in force; e.g., Gregorian,
	Islamic, Japanese, Chinese, Hebrew etc. as well as local
	ordering conventions such as UK: day/month/year, US:
	month/day/year.

	Inputting and outputting dates on computers is greatly
	complicated by these {localisation} issues which is why they
	tend to operate on dates internally in some unified form such
	as seconds past midnight at the start of the first of January
	1970.

	Many software and hardware representations of dates allow only
	two digits for the year, leading to the {year 2000} problem.

	{Unix manual page}: date(1), ctime(3).

	(1997-07-11)

| hour | sessions | hits |
+------------+------+----------+------+
| 2007-09-02 | 10 | 6 | 140 |
+------------+------+----------+------+
| 2007-09-03 | 10 | 6 | 140 |
+------------+------+----------+------+
| 2007-10-19 | 10 | 6 | 140 |
+------------+------+----------+------+
| 2007-10-19 | 11 | 2 | 54 |
+------------+------+----------+------+
| 2007-10-22 | 15 | 1 | 22 |
+------------+------+----------+------+
| 2007-10-22 | 16 | 5 | 150 |
+------------+------+----------+------+
| 2007-10-23 | 12 | 6 | 210 |
+------------+------+----------+------+
| 2007-10-24 | 19 | 4 | 53 |
+------------+------+----------+------+
| 2007-10-24 | 13 | 8 | 65 |
+------------+------+----------+------+
| 2007-10-25 | 7 | 2 | 37 |
+------------+------+----------+------+
| 2007-10-26 | 7 | 3 | 10 |
+------------+------+----------+------+
| 2007-10-26 | 9 | 1 | 17 |
+------------+------+----------+------+

I want to count the number of hits and sessions in the past week, so I
wrote in message:

SELECT `date`, SUM(hits), SUM(sessions)
FROM stats_dt
WHERE YEARWEEK(`date`) = YEARWEEK(CURDATE())
GROUP BY `date` DESC

The resultset looks like:

+------------+---------------+-----------+
| date | SUM(sessions) | SUM(hits) |
+------------+---------------+-----------+
| 2007-10-22 | 6 | 172 |
+------------+---------------+-----------+
| 2007-10-23 | 6 | 210 |
+------------+---------------+-----------+
| 2007-10-24 | 12 | 118 |
+------------+---------------+-----------+
| 2007-10-25 | 2 | 37 |
+------------+---------------+-----------+

As expected, but not entirely what I want: I want to get the SUM of
all hits and sessions, like this:

+----------+------+
| sessions | hits |
+----------+------+
| 26 | 537 |
+----------+------+

I do not understand how to translate this into SQL. I tried this but
MySQL returned a big error:

SELECT SUM(SUM(hits)), SUM(SUM(sessions))
FROM stats_dt
WHERE YEARWEEK(`date`) = YEARWEEK(CURDATE())

Could anybody help me out here? All help is appreciated :-)

From: J.O. Aho

Date: Saturday, October 27, 2007

wrote in message:

I want to count the number of hits and sessions in the past week, so I
wrote in message:
SELECT `date`, SUM(hits), SUM(sessions)
FROM stats_dt
WHERE YEARWEEK(`date`) = YEARWEEK(CURDATE())
GROUP BY `date` DESC
The resultset looks like:
+------------+---------------+-----------+
| date | SUM(sessions) | SUM(hits) |
+------------+---------------+-----------+
| 2007-10-22 | 6 | 172 |
+------------+---------------+-----------+
| 2007-10-23 | 6 | 210 |
+------------+---------------+-----------+
| 2007-10-24 | 12 | 118 |
+------------+---------------+-----------+
| 2007-10-25 | 2 | 37 |
+------------+---------------+-----------+
As expected, but not entirely what I want: I want to get the SUM of
all hits and sessions, like this:
+----------+------+
| sessions | hits |
+----------+------+
| 26 | 537 |
+----------+------+

Do not group--

//Aho

From: dennis.sprengers

Date: Saturday, October 27, 2007

Thanks!

* frown * stupid me...

Next Message: Finding locking process

Blogs related to Sum all sums

mysql php date insert
... mysql php select tutorial mysql php select where mysql php server mysql php set names mysql php set names utf8 mysql php site web mysql php software mysql php sort mysql php space web mysql php sum mysql php sydney training mysql ...

Database Performance Optimization - Denormalization using MySQL ...
In plain english - calculate the sum of all transactions for customer X, where the type of transaction is a deposit-transaction. We are linking between the customers and transactions tables using the customer_id. What's the big deal? ...

[gislist] SUM: CMS recommendations for web GIS integration
Both use PHP and MySQL with Drupal also supporting Postgres. We are more familiar with PHP than Python (Plone) and any custom apps we build using Python or other languages (JSP) will probably just be linked from within the CMS site." ...

MySQL Reference
Returns the standard deviation of the values in expression (eg, SELECT STDDEV(points) FROM data). SUM( expression). Returns the sum of the values in expression (eg, SELECT SUM(calories) FROM daily_diet). ...

http://dev.mysql.com/doc/mysql/en/string-functions.html
Cory : I'd like to SUM all the results of that operation. Can this be done' Cory : I tried SUM( project_task_hours.hours * project_task.rate ) CoryJ : Is anyone here' CoryJ : SELECT employee.name, CoryJ : project_task_hours.hours, ...

Ive created a cms php page that queries a mysql database there is ...
i want all the AccountNames and the sum of the TransferBytes or 0 if there’s no values for that acount in Transfers. your sql server might not want to accept connections form your host or client, w/e. So how would I fix that? ...