SQL: do math in query

Ephracis · 04-25-2009, 12:28 AM

Hi,

I have a table with a list of votes.
Each vote has an item id (the item that the votes refers to) and a value (either 1 or -1).

I want to select all votes, group by item_id, and count all values together.

For example:

There's 51 votes for item_id=1 with value=1
There's 50 votes for item_id=1 with value=-1
There's 5 votes for item_id=2 with value=1
There's 2 votes for item_id=2 with value=-1
There's 4 votes for item_id=3 with value=-1

I want a query that gives me this:

Code:

item_id .. vote_sum

.. 2 ........ 3
.. 1 ........ 1
.. 3 ....... -4

giftlftr_23 · 04-25-2009, 01:23 AM

You can use the count() function of SQL.
For example:

SELECT COUNT(item_id) AS votesum, item_id FROM table_votes WHERE value=1 GROUP BY item_id

Hope it helps.

Ephracis · 04-25-2009, 01:41 AM

Quote:

Originally Posted by giftlftr_23

You can use the count() function of SQL.
For example:

SELECT COUNT(item_id) AS votesum, item_id FROM table_votes WHERE value=1 GROUP BY item_id

Hope it helps.

No, this is not what I was looking for. This will count all positive votes. I wanted a way to do the math (all positive votes minus all negative votes) inside the SQL statement.

Like "count(*) where value=1" - "count(*) where value=-1"

As I wrote above the result should be:

Code:

item_id .. vote_sum

.. 2 ........ 3 (5 - 2)
.. 1 ........ 1 (51 - 50)
.. 3 ....... -4 (0 - 4)

(I added the math to clarify)

aspire1 · 04-25-2009, 01:50 AM

Use the sum() function.

giftlftr_23 · 04-25-2009, 02:47 AM

The sum will do job. try:

SELECT item_id, SUM(value) AS vote_sum FROM table_votes WHERE item_id=id GROUP BY item_id