i am successfully using the following query to select the number of hours an
employee works on each project per month:
mysql> SELECT SUM(IF(MONTH(period)=01,hours,0)) as 'Jan',
SUM(IF(MONTH(period)=02,hours,0)) as 'Feb', . . . SUM(IF(MONTH
(period)=12,hours,0)) as 'Dec' FROM log WHERE id = '12345
name',
> SUM(log.hours) AS 'hours'
> FROM log LEFT OUTER JOIN empnum
> ON log.id = empnum.id
> WHERE log.pj = '$pj' GROUP BY id;
-Original Message-
From: DL Neil [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 05, 2002 6:42 PM
To: Forer, Leif; 'Nathan
DN-
I'm not sure what you mean by "tbl2's population is a sub-set of that
in tbl1". tbl1 contains user id nums and hours each user spent working on a
project. tbl2 contains user id nums and the first and last names that
correspond to each user id. There are user id nums in tbl1 that do not
exi
I want to select and join data from two tables if the same id exists in both
tables. If the id only exists in one table I want MySQL to return a string
like "sorry, no records found". How can I do this?
I'm guessing it's something like:
mysql> SELECT tbl1.this, tbl2.that FROM tbl1, tbl2 WHERE
I'm performing a query on 2 tables:
mysql> SELECT log.id, SUM (log.hours), empnum.lname
> FROM log, empnum
> WHERE log.id = '26009'
> AND log.id=empnum.id
> GROUP BY hours;
Empty set (0.01 sec)
The log table contains id numbers (plus a bunch of other data) but no
corresponding names. The name
econd query to get the grand total hours.
mysql> SELECT SUM ( hours ) AS 'g_total'
> FROM log WHERE id='$id' AND YEAR ( period ) >= '$sy' AND YEAR ( period ) <
'$ey';
Now, if you know a way that MySQL can extract the grand total from the first
Oops. The reason I wasn't able to get past 12 had nothing to do with 12. I
was saying:
select sum(if(month(period)=12,hours,0)) as Dec from log group by pj;
the Dec was the problem. of course, a simple renaming fixes the prob.
-Original Message-
From: Forer, Leif
Sent: Thu
?
And, it shouldn't be a big deal to just execute a second and third query
that does the row totals for each line and a grand total of all the
subtotals.
-Original Message-
From: DL Neil [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 28, 2002 1:16 PM
To: Forer, Leif; [EMAIL PROTECT
this is working:
mysql> select distinct pj from tmp;
+---+
| pj|
+---+
| 41857 |
| 41992 |
| 42620 |
| HOLID |
| OTHER |
| PERS |
| SICK |
| VACAT |
+---+
8 rows in set (0.00 sec)
now, i want to do something like this:
mysql> select sum(hours) from tmp where pj=distinct group b
Boy, that was easy. I just figured it out after sending the question:
mysql> SELECT period,hours FROM log GROUP BY MONTH(period);
-Original Message-
From: Forer, Leif
Sent: Tuesday, February 26, 2002 5:32 PM
To: '[EMAIL PROTECTED]'
Subject: group by month
There is a dat
There is a date column in a table and I want to select data from the table
by month (instead of by every single entry within each month).
Here's a sample of the table:
mysql> SELECT period,hours FROM log GROUP BY period;
++---+
| period| hours |
++---+
| 1
11 matches
Mail list logo
