Count Question
I have a table that lists employees by an identifier (BWE1, BWN1, BWE2, or
BWN2) See Below for example. What I want like to do is get a count of all
(BWE1 and BWN1) and a count of all (BWE2 and BWN2) employees. I.E. I just
want two results. I have done this using two different querys, but I would
like to make it into one. Does anyone have any clues on how this can be
done?
I would like to make these two querys into one.
SELECT COUNT(*) bw, id, FROM employee WHERE id IN("BWE1, BWE1") GROUP BY id
SELECT COUNT(*) bw, id, FROM employee WHERE id IN("BWE2, BWE2") GROUP BY id
EXAMPLE TABLE
empnumber, id
1, BWE1
2, BWN1
5, BWE2
6, BWN2
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
You will have to use 2 selects, but you could have both in 1 sql.
Example:
UNLOAD TO "output1.out"
SELECT count(*) from employee
WHERE id IN ("A", "B")
UNLOAD TO "output2.out"
SELECT count(*) from employee
WHERE id IN ("C", "D")
kjacobs@pharmacy.com wrote:
> I have a table that lists employees by an identifier (BWE1, BWN1, BWE2, or
> BWN2) See Below for example. What I want like to do is get a count of all
> (BWE1 and BWN1) and a count of all (BWE2 and BWN2) employees. I.E. I just
> want two results. I have done this using two different querys, but I would
> like to make it into one. Does anyone have any clues on how this can be
> done?
>
> I would like to make these two querys into one.
>
> SELECT COUNT(*) bw, id, FROM employee WHERE id IN("BWE1, BWE1") GROUP BY id
> SELECT COUNT(*) bw, id, FROM employee WHERE id IN("BWE2, BWE2") GROUP BY id>
> EXAMPLE TABLE
> empnumber, id
> 1, BWE1
> 2, BWN1
> 5, BWE2
> 6, BWN2
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
kjacobs@pharmacy.com wrote:
>
> I have a table that lists employees by an identifier (BWE1, BWN1, BWE2,
> or BWN2) See Below for example. What I want like to do is get a count
> of all (BWE1 and BWN1) and a count of all (BWE2 and BWN2) employees.
> I.E. I just want two results. I have done this using two different
> querys, but I would like to make it into one. Does anyone have any
> clues on how this can be done?
>
> I would like to make these two querys into one.
>
> SELECT COUNT(*) bw, id FROM employee
> WHERE id IN("BWE1, BWN1")
> GROUP BY id;
> SELECT COUNT(*) bw, id FROM employee
> WHERE id IN("BWE2, BWN2")
> GROUP BY id>
> EXAMPLE TABLE
> empnumber, id
> 1, BWE1
> 2, BWN1
> 5, BWE2
> 6, BWN2
I took to liberty of editing & correcting the obvious syntax errors in
the sample SQL. I also made the assumption that when you wrote:
WHERE id IN("BWE1, BWE1")
you meant:
WHERE id IN("BWE1, BWN1")
Of course I have not tried this but I have played with syntax that looks
like the following:
select count(*) bw, id
from employee
where id matches "BW[NE][12]"
group by id;
Give it a shot and give us a shout. ;-)
--
-- Jake (Retrospectively realizes there is no future in hindsight)
+------------------------------------------------------------+
| The expedient performance of a task with excessive concern |
| regarding its duration-to-completion engenders a virtual |
| certainty of diminished benefit therefrom. |
| -- Benjamin Franklin (but he said it in 3 words) |
+------------------------------------------------------------+
kjacobs@pharmacy.com wrote:
> I have a table that lists employees by an identifier (BWE1, BWN1, BWE2, or
> BWN2) See Below for example. What I want like to do is get a count of all
> (BWE1 and BWN1) and a count of all (BWE2 and BWN2) employees. I.E. I just
> want two results. I have done this using two different querys, but I would
> like to make it into one. Does anyone have any clues on how this can be
> done?
>
> I would like to make these two querys into one.
>
> SELECT COUNT(*) bw, id, FROM employee WHERE id IN("BWE1, BWE1") GROUP BY id
> SELECT COUNT(*) bw, id, FROM employee WHERE id IN("BWE2, BWE2") GROUP BY id>
> EXAMPLE TABLE
> empnumber, id
> 1, BWE1
> 2, BWN1
> 5, BWE2
> 6, BWN2
Huh? I am definitely missing something here. What two results do you want?
You have four results. Assuming you actually mean:
SELECT COUNT(*) bw, id FROM employee
WHERE id IN("BWE1", "BWN1")
GROUP BY id
and
SELECT COUNT(*) bw, id FROM employee
WHERE id IN("BWE2", "BWN2")
GROUP BY idto combine these into one query, you could just do:
SELECT COUNT(*) bw, id FROM employee
WHERE id IN("BWE1", "BWN1", "BWE2", "BWN2")
GROUP BY id
Either of the above would give you
bw id
1 BWE1
1 BWN1
1 BWE2
1 BWN2
based on your above data (which, of course, is not complete enough to give a
meaningful example).
If what you want is:
count ids
2 BWE1+BWN1
2 BWE2+BWN2
which is what your problem description sounded like, minus the example query,
then you'll need something like:
SELECT COUNT(*) count, 'BWE1+BWN1' ids FROM employee
WHERE id IN ("BWE1", "BWN1")
UNION
SELECT COUNT(*) count, 'BWE2+BWN2' ids FROM employee
WHERE id IN ("BWE2", "BWN2")
or maybe (because I hate hard-coding stuff like that):
SELECT COUNT(*) count, id[1,2] || '-' || id[4,4] ids FROM employee
GROUP BY 2which will give you
count ids
2 BW-1
2 BW-2
June
--
june_t@hotmail.com
Grounded in Palo Alto, living on KitKat bars
kjacobs@pharmacy.com wrote:
> I have a table that lists employees by an identifier (BWE1, BWN1,
> BWE2, or BWN2) See Below for example. What I want like to do is
> get a count of all (BWE1 and BWN1) and a count of all (BWE2 and
> BWN2) employees. I.E. I just want two results. I have done this
> using two different querys, but I would like to make it into one.
> Does anyone have any clues on how this can be done?
>
> I would like to make these two querys into one.
>
> SELECT COUNT(*) bw, id, FROM employee
> WHERE id IN("BWE1, BWE1") GROUP BY id
> SELECT COUNT(*) bw, id, FROM employee
> WHERE id IN("BWE2, BWE2") GROUP BY id>
> EXAMPLE TABLE
> empnumber, id
> 1, BWE1
> 2, BWN1
> 5, BWE2
> 6, BWN2
Well, I've seen 3 different answers, none of which answers the
exactly the question I think I'm answering (or the same question
as any of the other answers). So I'm probably misunderstanding
the question too :-(
There's a couple of semantic problems (but no syntax errors -- the
code is syntactically correct) in the two sample queries, which
should probably read:
SELECT COUNT(*) bw, id, FROM employee
WHERE id IN("BWE1", "BWN1") GROUP BY id
SELECT COUNT(*) bw, id, FROM employee
WHERE id IN("BWE2", "BWN2") GROUP BY id
However, each of these queries produces two rows of data for a total
of four rows, whereas I think the questioner seeks to get the answer
2 for the pair of id's BWE1 and BWN1, and also the answer two for the
pair of id's BWE2 and BWN2, using the sample data table given.
If I'm correct, then we need to be able to group by the first,
second and fourth characters of the ID in this particular example:
SELECT COUNT(*) bw, id[1,2], id[4]
FROM employee
WHERE id IN("BWE1", "BWN1")
GROUP BY id[1,2], id[4]
UNION
SELECT COUNT(*) bw, id[1,2], id[4]
FROM employee
WHERE id IN("BWE2", "BWN2")
GROUP BY id[1,2], id[4]
However, this solution only works because of the coding scheme
in the sample table and does not generalize very well. A more
general solution uses a coding table:
CREATE TABLE GroupCoding
(
GroupCode CHAR(3) NOT NULL,
GroupEntry CHAR(4) NOT NULL,
PRIMARY KEY(GroupCode, GroupEntry)
)
For the sample data:
INSERT INTO GroupCoding VALUES("BW1", "BWE1");
INSERT INTO GroupCoding VALUES("BW1", "BWN1");
INSERT INTO GroupCoding VALUES("BW2", "BWE2");
INSERT INTO GroupCoding VALUES("BW2", "BWN2");
The single query can then be:
SELECT C.GroupCode, COUNT(*)
FROM Employee E, GroupCode C
WHERE E.Id = C.GroupEntry
GROUP BY C.GroupCode;
This would produce the answer:
BW1 2
BW2 2
for the given data, unless I've made a gross mistake somewhere, which
is not impossible since I've not tested any of my code.
--
Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net)
Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN
#include <disclaimer.h>