select rows and a sum from a single mysql table in one query

Question

Recently, I'm trying to decrease the number of queries by joining unnecessary ones.

in this very case I came to a query that I should have used two queries : one to get the sum of all options and the other is the rows of the same table

SELECT `id`, `text`,`count`, SUM(`count`) AS sumoption
    FROM _options

But when I'm trying to run this query , the result will be only one row , and I think its because I added SUM(count) . I know i should use group by to solve this issue but the table schema is not that simple .

id  text                  count
 1  Honda                   1        
 2  Benz                    0        
 3  Toyota                  1

now the sum should be 2 and the it should list all options and their values.
How can I make this happen ?

PS. : The expected outcome :

 Honda                   1        
 Benz                    0        
 Toyota                  1  
 Sum of counts : 2

It isn't clear why you can't use a GROUP BY Please post an example of what the output should be from the data you posted above. — Michael Berkowski
– Michael Berkowski, Commented Nov 5, 2011 at 12:51
the output as i said will be a single row and I want to list all rows in a loop — Mac Taylor
– Mac Taylor, Commented Nov 5, 2011 at 12:53
I hate the fact that MySQL allows aggregate functions without group by clauses. — Pablo Santa Cruz
– Pablo Santa Cruz, Commented Nov 5, 2011 at 12:53
@MacTaylor That's what you said the query you tried produces. I meant we want to see an example of the output you intend to produce. — Michael Berkowski
– Michael Berkowski, Commented Nov 5, 2011 at 12:55
@PabloSantaCruz and allows aggregates across several columns without specifying all of them in the GROUP BY — Michael Berkowski
– Michael Berkowski, Commented Nov 5, 2011 at 12:56

Johan · Accepted Answer · 2011-11-05 13:06:11Z

3

Something like this looks more logical:

SELECT m.id, m.name, count(o.man_id) AS sumoption
FROM _options o 
RIGHT JOIN manufacturers m ON (m.id = o.man_id)
GROUP BY m.id WITH ROLLUP

In your case you can also use:

SELECT id, `text`, SUM(`count`) AS sumoption
FROM _options 
GROUP BY id WITH ROLLUP

answered Nov 5, 2011 at 13:06

Johan

77.4k28 gold badges204 silver badges346 bronze badges

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

3 Comments

Johan Over a year ago

@MacTaylor, I made a jump of happiness when I found out about that too.

Mac Taylor Over a year ago

nay , that was my mistake , it didn't work fully , it just brings two rows with the same name !!! I was so tired , I didn't notice.

Mac Taylor Over a year ago

I tried separate mysql queries to get both sum of the counts and list of rows

Collectives™ on Stack Overflow

select rows and a sum from a single mysql table in one query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related