Aggregating Data - Sum(year), Count(year), Sum(month), Count(month), Sum(today), Count(today)

Hi, I'm trying to find the following information from the table found
bellow.

Year_Sales - # of sales that have occurred this year
Year_Income - SUM(amount)
Month_Sales - # of sales that have occurred this month
Month_Income - SUM(amount)
Today_Sales -- # of sales that have occurred today
Today_Icome - SUM(amount)

My table looks like this (simplified):

CREATE TABLE ` Transaction` (
`id` int(10) unsigned NOT NULL auto_increment,
`date_time` datetime NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`amount` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY (`date_time `),
KEY `userId` (`userId`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

I'm working on a site that allows people to sell stuff and every time
a seller checks his account he'll see a report showing his
year_sales, year_income, month_sales, etc. Since this query will be
run fairly regularly I'm trying to build this query as efficient as
possible. Also over time the Transaction table will get quite big,
well hopefully J

So far here are the solutions I came up with.

1) Run three queries, (Q1) get year_* info, (Q2) get month_* info,
(Q3) get today_* info. Each query will use where date = .... clauses
to limit the date range accordingly. This feels like a horrible
solution.

2) Use temporary tables

a) Place the results from (Q1) into a temporary table (TQ1), find
year_sales and year_income
b) (Q2) will now query from the temporary table (TQ1) to create a new
temporary table (TQ2), then find month_sales, month_income.
c) (Q3) will now query from the temporary table (TQ2), find
today_sales, today_income.
d) Remove (TQ1), (TQ2)
This solution seems a bit better, but still doesn't feel right.

3) If there is a way to subquery your first

I'm open to suggestions! Thanks in advance :)

Jake

Re: Aggregating Data - Sum(year), Count(year), Sum(month), Count(month), Sum(today), Count(today)

The syntax may be off as this is from another DB system, but something
along the lines of the following might work for you, or at least give
you some ideas:

SELECT user_id
,SUM(
CASE WHEN date_time >= date_trunc('year',now()) THEN 1
ELSE 0
END
) AS sales_this_year
,SUM(
CASE WHEN date_time >= date_trunc('year',now()) THEN amount
ELSE 0
END
) AS income_this_year
,SUM(
CASE WHEN date_time >= date_trunc('month',now()) THEN 1
ELSE 0
END
) AS sales_this_month
,SUM(
CASE WHEN date_time >= date_trunc('month',now()) THEN amount
ELSE 0
END
) AS income_this_month
,SUM(
CASE WHEN date_time >= date_trunc('day',now()) THEN 1
ELSE 0
END
) AS sales_this_day
,SUM(
CASE WHEN date_time >= date_trunc('day',now()) THEN amount
ELSE 0
END
) AS income_this_day
FROM tx
GROUP BY 1
ORDER BY 1 ;


On Oct 28, 12:52 am, crane.j...@gmail.com wrote:
> Hi, I'm trying to find the following information from the table found
> bellow.
>
> Year_Sales - # of sales that have occurred this year
> Year_Income - SUM(amount)
> Month_Sales - # of sales that have occurred this month
> Month_Income - SUM(amount)
> Today_Sales -- # of sales that have occurred today
> Today_Icome - SUM(amount)
>
> My table looks like this (simplified):
>
> CREATE TABLE ` Transaction` (
> `id` int(10) unsigned NOT NULL auto_increment,
> `date_time` datetime NOT NULL,
> `user_id` int(10) unsigned NOT NULL,
> `amount` int(10) unsigned NOT NULL default '0',
> PRIMARY KEY (`id`),
> KEY (`date_time `),
> KEY `userId` (`userId`),
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
>
> I'm working on a site that allows people to sell stuff and every time
> a seller checks his account he'll see a report showing his
> year_sales, year_income, month_sales, etc. Since this query will be
> run fairly regularly I'm trying to build this query as efficient as
> possible. Also over time the Transaction table will get quite big,
> well hopefully J
>
> So far here are the solutions I came up with.
>
> 1) Run three queries, (Q1) get year_* info, (Q2) get month_* info,
> (Q3) get today_* info. Each query will use where date = .... clauses
> to limit the date range accordingly. This feels like a horrible
> solution.
>
> 2) Use temporary tables
>
> a) Place the results from (Q1) into a temporary table (TQ1), find
> year_sales and year_income
> b) (Q2) will now query from the temporary table (TQ1) to create a new
> temporary table (TQ2), then find month_sales, month_income.
> c) (Q3) will now query from the temporary table (TQ2), find
> today_sales, today_income.
> d) Remove (TQ1), (TQ2)
> This solution seems a bit better, but still doesn't feel right.
>
> 3) If there is a way to subquery your first
>
> I'm open to suggestions! Thanks in advance :)
>
> Jake

Re: Aggregating Data - Sum(year), Count(year), Sum(month), Count(month), Sum(today), Count(today)

Interesting, thanks. I'll play with this a bit. I need to run this
query against a set of products but that should be doable with a join I
suppose.

Thanks a lot!
Jake

shakahshakah@gmail.com wrote:
> The syntax may be off as this is from another DB system, but something
> along the lines of the following might work for you, or at least give
> you some ideas:
>
> SELECT user_id
> ,SUM(
> CASE WHEN date_time >= date_trunc('year',now()) THEN 1
> ELSE 0
> END
> ) AS sales_this_year
> ,SUM(
> CASE WHEN date_time >= date_trunc('year',now()) THEN amount
> ELSE 0
> END
> ) AS income_this_year
> ,SUM(
> CASE WHEN date_time >= date_trunc('month',now()) THEN 1
> ELSE 0
> END
> ) AS sales_this_month
> ,SUM(
> CASE WHEN date_time >= date_trunc('month',now()) THEN amount
> ELSE 0
> END
> ) AS income_this_month
> ,SUM(
> CASE WHEN date_time >= date_trunc('day',now()) THEN 1
> ELSE 0
> END
> ) AS sales_this_day
> ,SUM(
> CASE WHEN date_time >= date_trunc('day',now()) THEN amount
> ELSE 0
> END
> ) AS income_this_day
> FROM tx
> GROUP BY 1
> ORDER BY 1 ;
>
>
> On Oct 28, 12:52 am, crane.j...@gmail.com wrote:
> > Hi, I'm trying to find the following information from the table found
> > bellow.
> >
> > Year_Sales - # of sales that have occurred this year
> > Year_Income - SUM(amount)
> > Month_Sales - # of sales that have occurred this month
> > Month_Income - SUM(amount)
> > Today_Sales -- # of sales that have occurred today
> > Today_Icome - SUM(amount)
> >
> > My table looks like this (simplified):
> >
> > CREATE TABLE ` Transaction` (
> > `id` int(10) unsigned NOT NULL auto_increment,
> > `date_time` datetime NOT NULL,
> > `user_id` int(10) unsigned NOT NULL,
> > `amount` int(10) unsigned NOT NULL default '0',
> > PRIMARY KEY (`id`),
> > KEY (`date_time `),
> > KEY `userId` (`userId`),
> > ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
> >
> > I'm working on a site that allows people to sell stuff and every time
> > a seller checks his account he'll see a report showing his
> > year_sales, year_income, month_sales, etc. Since this query will be
> > run fairly regularly I'm trying to build this query as efficient as
> > possible. Also over time the Transaction table will get quite big,
> > well hopefully J
> >
> > So far here are the solutions I came up with.
> >
> > 1) Run three queries, (Q1) get year_* info, (Q2) get month_* info,
> > (Q3) get today_* info. Each query will use where date = .... clauses
> > to limit the date range accordingly. This feels like a horrible
> > solution.
> >
> > 2) Use temporary tables
> >
> > a) Place the results from (Q1) into a temporary table (TQ1), find
> > year_sales and year_income
> > b) (Q2) will now query from the temporary table (TQ1) to create a new
> > temporary table (TQ2), then find month_sales, month_income.
> > c) (Q3) will now query from the temporary table (TQ2), find
> > today_sales, today_income.
> > d) Remove (TQ1), (TQ2)
> > This solution seems a bit better, but still doesn't feel right.
> >
> > 3) If there is a way to subquery your first
> >
> > I'm open to suggestions! Thanks in advance :)
> >
> > Jake

Re: Aggregating Data - Sum(year), Count(year), Sum(month), Count(month), Sum(today), Count(today)

On Fri, 27 Oct 2006 21:52:15 -0700, crane.jake wrote:

> Hi, I'm trying to find the following information from the table found
> bellow.
>
> Year_Sales - # of sales that have occurred this year
> Year_Income - SUM(amount)
> Month_Sales - # of sales that have occurred this month
> Month_Income - SUM(amount)
> Today_Sales -- # of sales that have occurred today
> Today_Icome - SUM(amount)
>
> My table looks like this (simplified):
>
> CREATE TABLE ` Transaction` (
> `id` int(10) unsigned NOT NULL auto_increment,
> `date_time` datetime NOT NULL,
> `user_id` int(10) unsigned NOT NULL,
> `amount` int(10) unsigned NOT NULL default '0',
> PRIMARY KEY (`id`),
> KEY (`date_time `),
> KEY `userId` (`userId`),
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
>
> I'm working on a site that allows people to sell stuff and every time
> a seller checks his account he'll see a report showing his
> year_sales, year_income, month_sales, etc. Since this query will be
> run fairly regularly I'm trying to build this query as efficient as
> possible. Also over time the Transaction table will get quite big,
> well hopefully J
>
> So far here are the solutions I came up with.
>
> 1) Run three queries, (Q1) get year_* info, (Q2) get month_* info,
> (Q3) get today_* info. Each query will use where date = .... clauses
> to limit the date range accordingly. This feels like a horrible
> solution.
>
> 2) Use temporary tables
>
> a) Place the results from (Q1) into a temporary table (TQ1), find
> year_sales and year_income
> b) (Q2) will now query from the temporary table (TQ1) to create a new
> temporary table (TQ2), then find month_sales, month_income.
> c) (Q3) will now query from the temporary table (TQ2), find
> today_sales, today_income.
> d) Remove (TQ1), (TQ2)
> This solution seems a bit better, but still doesn't feel right.
>
> 3) If there is a way to subquery your first
>
> I'm open to suggestions! Thanks in advance :)

Well - Temporary tables seem feel right to me!
The results of each query are short and sweet and temporary tables are
made for such situations..

Subqueries work fine in later versions of MySQL and you can use them to
wrap everything in one efficient (but ugly!) SELECT query.

However -

Subqueries employ temporary tables behind the scene anyway. So
what's wrong with a series of short, easy to debug, queries
going into temporary tables?

It's what temporary tables are for ;-)
Thomas Bartkus

Re: Aggregating Data - Sum(year), Count(year), Sum(month), Count(month), Sum(today), Count(today)

Hi Thomas,

Thanks for your feedback.

I was trying really hard to figure out how to do this using sub queries
in one select. I'm using MySQL 5.0.24a so I have access to sub
selects. Are you familiar enough with them that you could show me an
example for this problem?

Maybe a simplified version, say how to find Income_Year and
Income_Month from the table in my original post. I'd be really
interested in seeing how this work. I couldn't seem to figure out how
you use a sub query to select from a result set found in the outer
query.

I agree this would be an ugly query but I would just be interested to
see how it is done. Though I do wonder if letting MySQL handling
creating and removing the temporary tables is more efficient?

Thanks!
Jake


Thomas Bartkus wrote:
> On Fri, 27 Oct 2006 21:52:15 -0700, crane.jake wrote:
>
> > Hi, I'm trying to find the following information from the table found
> > bellow.
> >
> > Year_Sales - # of sales that have occurred this year
> > Year_Income - SUM(amount)
> > Month_Sales - # of sales that have occurred this month
> > Month_Income - SUM(amount)
> > Today_Sales -- # of sales that have occurred today
> > Today_Icome - SUM(amount)
> >
> > My table looks like this (simplified):
> >
> > CREATE TABLE ` Transaction` (
> > `id` int(10) unsigned NOT NULL auto_increment,
> > `date_time` datetime NOT NULL,
> > `user_id` int(10) unsigned NOT NULL,
> > `amount` int(10) unsigned NOT NULL default '0',
> > PRIMARY KEY (`id`),
> > KEY (`date_time `),
> > KEY `userId` (`userId`),
> > ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
> >
> > I'm working on a site that allows people to sell stuff and every time
> > a seller checks his account he'll see a report showing his
> > year_sales, year_income, month_sales, etc. Since this query will be
> > run fairly regularly I'm trying to build this query as efficient as
> > possible. Also over time the Transaction table will get quite big,
> > well hopefully J
> >
> > So far here are the solutions I came up with.
> >
> > 1) Run three queries, (Q1) get year_* info, (Q2) get month_* info,
> > (Q3) get today_* info. Each query will use where date = .... clauses
> > to limit the date range accordingly. This feels like a horrible
> > solution.
> >
> > 2) Use temporary tables
> >
> > a) Place the results from (Q1) into a temporary table (TQ1), find
> > year_sales and year_income
> > b) (Q2) will now query from the temporary table (TQ1) to create a new
> > temporary table (TQ2), then find month_sales, month_income.
> > c) (Q3) will now query from the temporary table (TQ2), find
> > today_sales, today_income.
> > d) Remove (TQ1), (TQ2)
> > This solution seems a bit better, but still doesn't feel right.
> >
> > 3) If there is a way to subquery your first
> >
> > I'm open to suggestions! Thanks in advance :)
>
> Well - Temporary tables seem feel right to me!
> The results of each query are short and sweet and temporary tables are
> made for such situations..
>
> Subqueries work fine in later versions of MySQL and you can use them to
> wrap everything in one efficient (but ugly!) SELECT query.
>
> However -
>
> Subqueries employ temporary tables behind the scene anyway. So
> what's wrong with a series of short, easy to debug, queries
> going into temporary tables?
>
> It's what temporary tables are for ;-)
> Thomas Bartkus