CODEWITHSUNDEEP
mysqlsqlaggregate-functions
themrhornet
themrhornet

Reputation: 92

Summation of rows

I am trying to maintain a database for a collection of items. Items and values are read from the collection of files. I want summation of frequency of repeating item in the table.

if i have a table like

type   frequency
---------------
bar       10
soap      25
bar       13

then I need the table to be

type   frequency
---------------
bar       23
soap      25

How do I update such table?

I tried 

SELECT
    type,
    SUM(frequency)
FROM
   MyTable

but output is like

type    frequency
-----------------
bar       48

Upvotes: 1

Views: 69

Answers (2)

John Ruddell
John Ruddell

Reputation: 25862

You are missing a simple GROUP BY on your query to tell the aggregate function SUM() how to group the data. Without a GROUP BY your aggregate function will just sum all of the rows together.

UPDATE table t,
(   
    SELECT type, SUM(frequency) as frequency
    FROM table
    GROUP BY type
) t1

SET t.frequency = t1.frequency WHERE t.type = t1.type

This is a way you can update the table with a sum of each type.

Your update will cause duplicate entries in the database for every type. A simple fix for this is an alter on the table to add a unique index that will not allow there to be more than one. You have to use IGNORE or else it will say you have a duplicate entry

ALTER IGNORE TABLE table ADD UNIQUE INDEX `my_special_idx` (type, frequency);

SQL Fiddle Example

Upvotes: 2

Willi Mentzel
Willi Mentzel

Reputation: 29904

You need GROUP BY for aggregate functions.

Try this:

SELECT
    type,
    SUM(frequency)
FROM
    MyTable
GROUP BY 
    type

Upvotes: 2

Related Questions