MySQL Server Forum - SUM and COUNT Questions: Grovel Grovel

Author

SUM and COUNT Questions: Grovel Grovel

Good Man

2005-06-02, 1:23 pm

Hi all

Well, I didn't want to have to bring this question to someone else to
answer, but I am truly flummoxed and could use some help. It all boils
down to trying to find the number of jobs available in a particular
state. I have two tables, one called STATES and one called WORK. Here
are excerpts from both:

STATES table:
StateID | StateName | StateAbbr
---------------------------------
1 Alabama AL
2 Alaska AK
44 Texas TX
etc....

WORK table:
WorkID | JobState | JobType
----------------------------
1 2 fireman
2 1 fireman
3 44 fireman
4 44 doctor

(so, job number 1 is located in alaska, job #2 is located in alabama, and
jobs number 3 & 4 are located in Texas)

Now, the original task was to come up with a count/listing of available
jobs in states that have work available:

"SELECT States.StateAbbr, COUNT(*) AS NumJobs FROM States RIGHT JOIN Work
ON States.StateID=Work.JobState GROUP BY Work.JobState ORDER BY
StateAbbr"

This gave me a result something like:

StateAbbr | NumJobs
----------------------
AL 1
AK 1
TX 2

(one 'work' entry for alabama and alaska, and two seperate entries for
work in texas)

NOW.... this all works fine, obviously, but it was designed under the
assumption that there would only be one job per entry in the WORK table.
However, now we'd like to specify number of jobs available for the
particular job type in the WORK table.... i added a column called
"JobsAvail" and now we have an example like:

WorkID | JobState | JobType | JobsAvail
----------------------------------------
1 2 fireman 1
2 1 fireman 1
3 2 police 4
4 44 fireman 1
5 44 doctor 2

So, according to this table, there are now 5 jobs available in alaska, 3
in texas and 1 in alabama. However, if I modify my SELECT statement to
something like:

"SELECT States.StateAbbr, SUM(Work.JobsAvail) AS NumResults FROM States
RIGHT JOIN Work ON States.StateID=Work.JobState GROUP BY Work.JobState
ORDER BY StateAbbr"

I still get the same results as earlier; NumJobs for alaska gives me '1'
instead of the '5' i was expecting, and NumJobs for texas gives me '2'
instead of the '3' i was expecting.

So, it all boils down to this question:

** How can I design my query to return the following result: **

StateAbbr | NumJobs
----------------------
AL 1
AK 5
TX 3

Thanks for reading, LONG bloody question I know, but like I said, I'm
just running circles around this and truly need some help...

Thanks!!! Oh, MySQL 4.1 on Linux

- GM

Bill Karwin

2005-06-02, 8:23 pm

Good Man wrote:
> "SELECT States.StateAbbr, SUM(Work.JobsAvail) AS NumResults FROM States
> RIGHT JOIN Work ON States.StateID=Work.JobState GROUP BY Work.JobState
> ORDER BY StateAbbr"
>
> I still get the same results as earlier; NumJobs for alaska gives me '1'
> instead of the '5' i was expecting, and NumJobs for texas gives me '2'
> instead of the '3' i was expecting.

I tried the example exactly as you gave it and it does seem to give the
desired output. Below I include the database dump of what I typed in,
with the query that gave the desired output. Try the query in the mysql
CLI and see if you get the desired results.

If so, is your query in a web app that isn't deploying correctly or
something? Try outputting some debugging-type harmless static string in
the web app and see if you can get that type of change to deploy.

Regards,
Bill K.

CREATE DATABASE `test`;
USE `test`;

DROP TABLE IF EXISTS `states`;
CREATE TABLE `states` (
`stateid` int(11) default NULL,
`statename` varchar(30) default NULL,
`stateabbr` varchar(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `states` (`stateid`,`statenam
e`,`stateabbr`) VALUES
(1,'Alabama','AL'),
(2,'Alaska','AK'),
(44,'Texax','TX');

DROP TABLE IF EXISTS `work`;
CREATE TABLE `work` (
`workid` int(11) default NULL,
`jobstate` varchar(20) default NULL,
`jobtype` varchar(30) default NULL,
`jobsavail` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `work` (`workid`,`jobstate`
,`jobtype`,`jobsavai
l`) VALUES
(1,'2','fireman',1),

(2,'1','fireman',1),

(3,'2','police',4),
(4,'44','fireman',1)
,
(5,'44','doctor',2);

SELECT States.StateAbbr, SUM(Work.JobsAvail) AS NumResults FROM States
RIGHT JOIN Work ON States.StateID=Work.JobState GROUP BY Work.JobState
ORDER BY StateAbbr;

Thomas Bartkus

2005-06-02, 8:23 pm

"Good Man" <heyho@letsgo.com> wrote in message
news:Xns9669964516BE
Dsonicyouth@216.196.97.131...
> Hi all
>
> Well, I didn't want to have to bring this question to someone else to
> answer, but I am truly flummoxed and could use some help. It all boils
> down to trying to find the number of jobs available in a particular
> state. I have two tables, one called STATES and one called WORK. Here
> are excerpts from both:
>
> STATES table:
> StateID | StateName | StateAbbr
> ---------------------------------
> 1 Alabama AL
> 2 Alaska AK
> 44 Texas TX
> etc....
>
>
> WORK table:
> WorkID | JobState | JobType
> ----------------------------
> 1 2 fireman
> 2 1 fireman
> 3 44 fireman
> 4 44 doctor
>
> (so, job number 1 is located in alaska, job #2 is located in alabama, and
> jobs number 3 & 4 are located in Texas)
>
>
> Now, the original task was to come up with a count/listing of available
> jobs in states that have work available:
>
> "SELECT States.StateAbbr, COUNT(*) AS NumJobs FROM States RIGHT JOIN Work
> ON States.StateID=Work.JobState GROUP BY Work.JobState ORDER BY
> StateAbbr"
>
> This gave me a result something like:
>
> StateAbbr | NumJobs
> ----------------------
> AL 1
> AK 1
> TX 2
>
> (one 'work' entry for alabama and alaska, and two seperate entries for
> work in texas)
>
>
> NOW.... this all works fine, obviously, but it was designed under the
> assumption that there would only be one job per entry in the WORK table.
> However, now we'd like to specify number of jobs available for the
> particular job type in the WORK table.... i added a column called
> "JobsAvail" and now we have an example like:
>
> WorkID | JobState | JobType | JobsAvail
> ----------------------------------------
> 1 2 fireman 1
> 2 1 fireman 1
> 3 2 police 4
> 4 44 fireman 1
> 5 44 doctor 2
>
> So, according to this table, there are now 5 jobs available in alaska, 3
> in texas and 1 in alabama. However, if I modify my SELECT statement to
> something like:
>
> "SELECT States.StateAbbr, SUM(Work.JobsAvail) AS NumResults FROM States
> RIGHT JOIN Work ON States.StateID=Work.JobState GROUP BY Work.JobState
> ORDER BY StateAbbr"
>
> I still get the same results as earlier; NumJobs for alaska gives me '1'
> instead of the '5' i was expecting, and NumJobs for texas gives me '2'
> instead of the '3' i was expecting.
>
> So, it all boils down to this question:
>
> ** How can I design my query to return the following result: **
>
> StateAbbr | NumJobs
> ----------------------
> AL 1
> AK 5
> TX 3
>

Some one else will doubtless give you one single query that swallows this
problem whole. I use temporary table to break things up into 2 or more
simpler steps.

# Get the count of jobs available in each state (JobState).
CREATE TEMPORARY TABLE tmp_JobCounts
SELECT JobState,
COUNT(WorkID) As NumJobs
FROM WORK
GROUP BY JobState;

You already knew how to get those counts. Notice that I didn't bother with
the state abreviations (yet!). Note, also, that this is a good candidate
for a temporary table. It's bound to be small. At least it would be a
surprise to me if we returned counts for more than 50 states!

# Replace the State code (JobState) in tmp_JobCounts with the state
abbreviation
# and show the results.
SELECT StateAbbr, NumJobs
FROM tmp_JobCounts
LEFT JOIN STATES
ON (tmp_JobCounts.JobState=STATES.StateID);
ORDER BY StateAbbr;

Our temporary table works just like any ordinary table except that it can go
away when we are done with it. Here we just join STATES to get at the
abreviations. I believe this gives the results you seek.

# Be neat and drop the temporary table
# Not strictly necessary since it will go bye bye automatically
# when the connection is closed.
DROP TABLE tmp_JobCounts;

I just like temporary tables and less complex sql statements. The simpleton
in me tends to lose track (fast!) when I try to accomplish more than 1 or 2
things in a single statement.

Thomas Bartkus

Good Man

2005-06-02, 8:23 pm

Bill Karwin <bill@karwin.com> wrote in
news:d7nmje02vnv@ene
ws4.newsguy.com:

> I tried the example exactly as you gave it and it does seem to give
> the desired output. Below I include the database dump of what I typed
> in, with the query that gave the desired output. Try the query in the
> mysql CLI and see if you get the desired results.

Thanks for your help. Would you believe I was just using bad data?

Seriously, open up a paypal account or something, you should be getting
paid for all the advice/troubleshooting/coding you do in this NG. Or, just
bank all that karma :)

Thanks again!!

Good Man

2005-06-02, 8:23 pm

"Thomas Bartkus" <tom@dtsam.com> wrote in
news:SIudnfDiX6DC-QLfRVn-qQ@telcove.net:

> I just like temporary tables and less complex sql statements. The
> simpleton in me tends to lose track (fast!) when I try to accomplish
> more than 1 or 2 things in a single statement.

Thanks Thomas, I appreciate the time and effort you put into solving my
problem.

On a similar point, I thought the use of 'temporary tables' was something
to avoid? I thought I read that somewhere along the way....

Thanks again.

Thomas Bartkus

2005-06-02, 8:23 pm

"Good Man" <heyho@letsgo.com> wrote in message
news:Xns9669A9B7672F
5sonicyouth@216.196.97.131...
> "Thomas Bartkus" <tom@dtsam.com> wrote in
> news:SIudnfDiX6DC-QLfRVn-qQ@telcove.net:
>
>
> Thanks Thomas, I appreciate the time and effort you put into solving my
> problem.
>
> On a similar point, I thought the use of 'temporary tables' was something
> to avoid? I thought I read that somewhere along the way....

If you can recall what you read, please pass it along to me. As long the
table you create is small, I don't think there is a downside. I'm not even
sure it's a problem if the tables are large! You can query, modify, and
even index them. They are indistinguishable from ordinary tables other than
the fact that they exist only locally to a particular connection. No one
else can see them. And frequently, that's exactly what the doctor ordered.

I learned, and got into the habit of using, temporary tables from the days
before sub queries were available in MySQL. Now that sub queries are
available, I find I like the temporary table workaround better. I don't
know MySQL internals - but I'd be willing to make a beer bet that MySQL is
using those same temp tables internally every time you call a sub query.

Thomas Bartkus

Bill Karwin

2005-06-02, 8:23 pm

Good Man wrote:
> On a similar point, I thought the use of 'temporary tables' was something
> to avoid? I thought I read that somewhere along the way....

Speaking for myself, I think it adds a layer of complexity. For
instance, what if you try to create the temp table and it has already
been created earlier in the life of your app? What if you are using
tranparent connection pooling, and you can't rely on your web app
getting the same db connection on the next request? These issues are
all solvable, but they distract me from the problems I'm trying to solve
with my application.

That said, there are some exotic problems that are truly easier to solve
with temp tables. And someone else commented today that they use temp
tables when using LOAD DATA INFILE; load the data, then validate it or
clean it, then migrate it to your persistent tables. That's a pretty
good use of temp tables.

Regards,
Bill K.

Sponsored Links