mysql count one value display multiple columns

Question

I couldnt find this, i'm sure its simple.

Table (196 rows)

tech, name, area, manager
------------------------------
Joe,mary,pa,sally
Kim,brad,ga,tim
kelly,Joe,pa,sally
Joe,jose,pa,sally

a tech is assigned to multiple name/area/managers. I want to do a report that shows all the rows of the table with a column showing the number of assignments for the tech.

My desired results

tech, name, area, manager, (count of number of tech assignments)
Joe,mary,pa,sally,2
Kim,brad,ga,tim,1
kelly,Joe,pa,sally,1
Joe,jose,pa,sally,2

There are 4 techs, 196 names, 12 areas and 20 managers. My result set should be 196 rows. — user1084424
– user1084424, Commented Apr 18, 2012 at 15:02
Table Tech,name,area, manager Joe, john, PA, Sally Joe, Mary, GA, Jose — user1084424
– user1084424, Commented Apr 18, 2012 at 15:05
I don't get it. If you have 196 distinct names, each with one tech assigned, that tech has exactly 1 assignment in that context. — bfavaretto
– bfavaretto, Commented Apr 18, 2012 at 15:07

bfavaretto · Accepted Answer · 2012-04-18 15:20:59Z

3

I'm guessing you want a subquery on the SELECT clause:

SELECT 
   name, 
   area, 
   manager, 
   (SELECT COUNT(*) FROM tablename WHERE tech = x.tech) AS assignments
FROM tablename x

And here is a possibly more efficient way to do the same thing:

SELECT 
   t.name, 
   t.area, 
   t.manager,
    sub.assignments 
FROM tablename t
INNER JOIN (
    SELECT tech, COUNT(*) AS assignments 
    FROM tablename 
    GROUP BY tech
 ) sub
ON sub.tech = t.tech

edited Apr 18, 2012 at 15:20

answered Apr 18, 2012 at 15:04

bfavaretto

72k18 gold badges118 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

chance · Accepted Answer · 2012-04-18 15:11:20Z

1

select 
    a.tech, a.name, a.area, a.manager,
    b.cnt
from table a, (
    select count(*) cnt, tech
    from table
    group by tech) b
where a.tech=b.tech;

Is this what you want?

answered Apr 18, 2012 at 15:11

chance

6,52714 gold badges48 silver badges71 bronze badges

4 Comments

bfavaretto Over a year ago

Now that I rewrote my query I realize we're saying the same thing. +1

chance Over a year ago

@bfavaretto but I like your first solution, it looks tidy.

user1084424 Over a year ago

Doesnt work, bad syntax, not sure where you are trying to go.

bfavaretto Over a year ago

I think joining a subquery will perform better, as my first solution will always rerun the subquery for each row.

Devtrix.net · Accepted Answer · 2012-04-18 15:17:50Z

0

SELECT tech, count(name) AS total_records FROM db_table GROUP BY tech

Not sure this is what you're looking for though. Oh yeah, looks like you really need to use subquery. Thanks for the examples, that helped.

edited Apr 18, 2012 at 15:17

answered Apr 18, 2012 at 15:12

Devtrix.net

7937 silver badges8 bronze badges

2 Comments

bfavaretto Over a year ago

That's a very sane way to count assignments per tech, but strangely the OP seems to be asking for something else.

Devtrix.net Over a year ago

Yes, you're correct, bfavaretto. The time i posted that answer, there were no examples and i misunderstood it :) I've already voted up another answer with subquery method. Thanks

Ωmega · Accepted Answer · 2012-04-18 15:29:47Z

0

SELECT tech, name, area, manager, assignments  
FROM table
  INNER JOIN ( 
    SELECT tech, COUNT(*) AS assignments  
    FROM table  
    GROUP BY tech 
  ) t 
USING (tech)

Explanation:

Select all columns from table and join them with table containing tech column and count of rows from table having particular tech. Keyword USING says how those two table merge.

edited Apr 18, 2012 at 15:29

answered Apr 18, 2012 at 15:21

Ωmega

44k35 gold badges143 silver badges213 bronze badges

Collectives™ on Stack Overflow

mysql count one value display multiple columns

4 Answers 4

Comments

4 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related