merging two querys

merging two querys

am 24.10.2006 09:09:01 von stefano.troiani

Hi all,

I have two query that I I would like to be one, but I can't figure out
how to do it. (mysql 3.23)

$sql = sprintf ("SELECT task,SUM(hours),login FROM activities WHERE
login='%s' group by login",$usuario);

$sql2 = sprintf ("SELECT SUM(hours) FROM activities WHERE login='%s'
AND month='%s' group by login",$usuario,$getdate);

Thank you for any help.

Stefano

Re: merging two querys

am 24.10.2006 15:12:52 von zac.carey

stefano.troiani@gmail.com wrote:
> Hi all,
>
> I have two query that I I would like to be one, but I can't figure out
> how to do it. (mysql 3.23)
>
> $sql = sprintf ("SELECT task,SUM(hours),login FROM activities WHERE
> login='%s' group by login",$usuario);
>
> $sql2 = sprintf ("SELECT SUM(hours) FROM activities WHERE login='%s'
> AND month='%s' group by login",$usuario,$getdate);
>
> Thank you for any help.
>
> Stefano

So which date are you expecting to see in your results?

Re: merging two querys

am 24.10.2006 15:55:15 von zac.carey

stefano.troiani@gmail.com wrote:
> Hi all,
>
> I have two query that I I would like to be one, but I can't figure out
> how to do it. (mysql 3.23)
>
> $sql = sprintf ("SELECT task,SUM(hours),login FROM activities WHERE
> login='%s' group by login",$usuario);
>
> $sql2 = sprintf ("SELECT SUM(hours) FROM activities WHERE login='%s'
> AND month='%s' group by login",$usuario,$getdate);
>
> Thank you for any help.
>
> Stefano

Sorry, scrub my last post.

This should give you a clue:

SELECT login, SUM( hours ) AS time, GROUP_CONCAT( task
ORDER BY task
SEPARATOR ', ' ) AS tasks
FROM activities
WHERE login = '%s'
AND date = '%s'
GROUP BY login
LIMIT 0 , 30

Re: merging two querys

am 25.10.2006 09:45:01 von stefano.troiani

strawberry wrote:
> stefano.troiani@gmail.com wrote:
> > Hi all,
> >
> > I have two query that I I would like to be one, but I can't figure out
> > how to do it. (mysql 3.23)
> >
> > $sql = sprintf ("SELECT task,SUM(hours),login FROM activities WHERE
> > login='%s' group by login",$usuario);
> >
> > $sql2 = sprintf ("SELECT SUM(hours) FROM activities WHERE login='%s'
> > AND month='%s' group by login",$usuario,$getdate);
> >
> > Thank you for any help.
> >
> > Stefano
>
> Sorry, scrub my last post.
>
> This should give you a clue:
>
> SELECT login, SUM( hours ) AS time, GROUP_CONCAT( task
> ORDER BY task
> SEPARATOR ', ' ) AS tasks
> FROM activities
> WHERE login = '%s'
> AND date = '%s'
> GROUP BY login
> LIMIT 0 , 30


thanks for your help, but I have 3.23 version of mysql and it does not
support GROUP_CONCAT.

Cheers,

stefano

Re: merging two querys

am 25.10.2006 15:46:01 von zac.carey

stefano.troiani@gmail.com wrote:
> strawberry wrote:
> > stefano.troiani@gmail.com wrote:
> > > Hi all,
> > >
> > > I have two query that I I would like to be one, but I can't figure out
> > > how to do it. (mysql 3.23)
> > >
> > > $sql = sprintf ("SELECT task,SUM(hours),login FROM activities WHERE
> > > login='%s' group by login",$usuario);
> > >
> > > $sql2 = sprintf ("SELECT SUM(hours) FROM activities WHERE login='%s'
> > > AND month='%s' group by login",$usuario,$getdate);
> > >
> > > Thank you for any help.
> > >
> > > Stefano
> >
> > Sorry, scrub my last post.
> >
> > This should give you a clue:
> >
> > SELECT login, SUM( hours ) AS time, GROUP_CONCAT( task
> > ORDER BY task
> > SEPARATOR ', ' ) AS tasks
> > FROM activities
> > WHERE login = '%s'
> > AND date = '%s'
> > GROUP BY login
> > LIMIT 0 , 30
>
>
> thanks for your help, but I have 3.23 version of mysql and it does not
> support GROUP_CONCAT.
>
> Cheers,
>
> stefano
3.23 !?!?!?!?!
UPGRADE!

Or, failing that:

SELECT @prev_login := NULL ;

SELECT login, SUM( hours ) AS time, MAX( @task :=
IF (
@prev_login = login, CONCAT_WS( ',', @task , task ) , task )
) AS task
FROM activities
WHERE login = '%s'
AND date = '%s'
GROUP BY login
LIMIT 0 , 30

Re: merging two querys

am 26.10.2006 10:15:55 von stefano.troiani

strawberry wrote:
> stefano.troiani@gmail.com wrote:
> > strawberry wrote:
> > > stefano.troiani@gmail.com wrote:
> > > > Hi all,
> > > >
> > > > I have two query that I I would like to be one, but I can't figure out
> > > > how to do it. (mysql 3.23)
> > > >
> > > > $sql = sprintf ("SELECT task,SUM(hours),login FROM activities WHERE
> > > > login='%s' group by login",$usuario);
> > > >
> > > > $sql2 = sprintf ("SELECT SUM(hours) FROM activities WHERE login='%s'
> > > > AND month='%s' group by login",$usuario,$getdate);
> > > >
> > > > Thank you for any help.
> > > >
> > > > Stefano
> > >
> > > Sorry, scrub my last post.
> > >
> > > This should give you a clue:
> > >
> > > SELECT login, SUM( hours ) AS time, GROUP_CONCAT( task
> > > ORDER BY task
> > > SEPARATOR ', ' ) AS tasks
> > > FROM activities
> > > WHERE login = '%s'
> > > AND date = '%s'
> > > GROUP BY login
> > > LIMIT 0 , 30
> >
> >
> > thanks for your help, but I have 3.23 version of mysql and it does not
> > support GROUP_CONCAT.
> >
> > Cheers,
> >
> > stefano
> 3.23 !?!?!?!?!
> UPGRADE!
>
> Or, failing that:
>
> SELECT @prev_login := NULL ;
>
> SELECT login, SUM( hours ) AS time, MAX( @task :=
> IF (
> @prev_login = login, CONCAT_WS( ',', @task , task ) , task )
> ) AS task
> FROM activities
> WHERE login = '%s'
> AND date = '%s'
> GROUP BY login
> LIMIT 0 , 30


Thanks, I will try this query, if you have a spare time, can you
explain me this query?
There are things that i don't understand like the first line and the
use of ':=' and '@'


stefano

Re: merging two querys

am 26.10.2006 20:26:30 von zac.carey

stefano.troiani@gmail.com wrote:
> strawberry wrote:
> > stefano.troiani@gmail.com wrote:
> > > strawberry wrote:
> > > > stefano.troiani@gmail.com wrote:
> > > > > Hi all,
> > > > >
> > > > > I have two query that I I would like to be one, but I can't figure out
> > > > > how to do it. (mysql 3.23)
> > > > >
> > > > > $sql = sprintf ("SELECT task,SUM(hours),login FROM activities WHERE
> > > > > login='%s' group by login",$usuario);
> > > > >
> > > > > $sql2 = sprintf ("SELECT SUM(hours) FROM activities WHERE login='%s'
> > > > > AND month='%s' group by login",$usuario,$getdate);
> > > > >
> > > > > Thank you for any help.
> > > > >
> > > > > Stefano
> > > >
> > > > Sorry, scrub my last post.
> > > >
> > > > This should give you a clue:
> > > >
> > > > SELECT login, SUM( hours ) AS time, GROUP_CONCAT( task
> > > > ORDER BY task
> > > > SEPARATOR ', ' ) AS tasks
> > > > FROM activities
> > > > WHERE login = '%s'
> > > > AND date = '%s'
> > > > GROUP BY login
> > > > LIMIT 0 , 30
> > >
> > >
> > > thanks for your help, but I have 3.23 version of mysql and it does not
> > > support GROUP_CONCAT.
> > >
> > > Cheers,
> > >
> > > stefano
> > 3.23 !?!?!?!?!
> > UPGRADE!
> >
> > Or, failing that:
> >
> > SELECT @prev_login := NULL ;
> >
> > SELECT login, SUM( hours ) AS time, MAX( @task :=
> > IF (
> > @prev_login = login, CONCAT_WS( ',', @task , task ) , task )
> > ) AS task
> > FROM activities
> > WHERE login = '%s'
> > AND date = '%s'
> > GROUP BY login
> > LIMIT 0 , 30
>
>
> Thanks, I will try this query, if you have a spare time, can you
> explain me this query?
> There are things that i don't understand like the first line and the
> use of ':=' and '@'
>
>
> stefano

This should shed some light on it:

http://dev.mysql.com/doc/refman/5.0/en/user-variables.html