PHP MySQL query (sum from different rows)

Question

I have a small MySQL database for an online booking calendar. Now I wanted to get some stats out of this calendar. I am adding to each entry in the calendar values (full paid, partly paid and a status (paid, unpaid, reserved, etc)).

I have attached an image of the screenshot. As you can see, there are 4 different custom_attribute_ids. ID 1 is saving the status, ID 2 the full price and ID 3 the price already paid. The column entity_id is saving it together. So e.g. all 4 entries with entity_id 232 belongs together.

I now what to display the following: 1. The Sum of all full prices (so custom_attribute_id 2). This I have done with this code:

$result = mysql_query('SELECT SUM(attribute_value) AS value_sum
FROM custom_attribute_values WHERE custom_attribute_id=2');
$row = mysql_fetch_assoc($result);
$sum = $row['value_sum'];

This is working and showing me the sum of all full prices entered in the calender.

With the same code I am also showing the sum of the already partly paid amounts.

But now the problem, I want to show the sum of the attribute_value depending on the status. So the code should summarize all values when the custom_attribute_id=2 AND the attribute_value of the relevant entity_id is "Reserviert".

Would be very nice, if somebody could help me, or at least let me know, if this is possible. I am not able to re-design the database, as this code is given from the calendar system.

Here the db as text:

ID custom_attribute_value_id attribute_value entity_id attribute_category
1124 1 Anfrage 233 1
1125 2 1188 233 1
1126 4 233 1
1127 3 015757817858 233 1
1053 1 Reserviert 232 1
1054 2 1700 232 1
1057 3 017697544266 232 1
1058 4 232 1
1039 2 573 231 1
1040 3 088259216300 231 1
1042 1 Reserviert 231 1
1037 3 0043676845617203 230 1
1045 2 2346,50 230 1
1046 1 Reserviert 230 1
1032 1 Anfrage 229 1
1033 2 474 229 1
1034 3 229 1
1027 1 Anfrage 228 1
1029 3 228 1
1030 2 588,50 228 1
1024 3 01729843043 227 1
1025 1 Reserviert 227 1
1023 2 990 227 1

You missed custom_attribute_id as the 2nd column name in the db dump, am I right? — piotrm
– piotrm, Commented Jun 15, 2015 at 21:21
Consider providing proper DDLs (and/or an SQL fiddle) and a desired result. — Strawberry
– Strawberry, Commented Jun 15, 2015 at 21:58
you're right @piotm, just forget the title "ID" for the first column — Tom B.
– Tom B., Commented Jun 16, 2015 at 10:09

piotrm · Accepted Answer · 2015-06-15 21:19:58Z

1

You need a self join so you can have both status and related full price in the same row for each entity. Basically you take a part of the table with only status data and another part of the table with only full price data and join them on the same entity id. On the resulting set you can easily get the sum while using WHERE to restrict rows to those with 'Reserviert' status.

SELECT SUM(t2.attribute_value) AS value_sum
FROM custom_attribute_values t1 JOIN custom_attribute_values t2
  ON t1.entity_id = t2.entity_id
  AND t1.custom_attribute_id = 1
  AND t2.custom_attribute_id = 2
WHERE t1.attribute_value = 'Reserviert'

In this query attribute_value holds status info for t1 and full price for t2.

edited Jun 15, 2015 at 21:19

answered Jun 15, 2015 at 21:08

piotrm

12.4k5 gold badges33 silver badges28 bronze badges

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

Comments

Axel Amthor · Accepted Answer · 2015-06-15 20:41:53Z

1

This should be

 $result = mysql_query('SELECT SUM(attribute_value) AS value_sum
             FROM custom_attribute_values 
             WHERE custom_attribute_id=2 AND entity_id = \'Reserviert\'');

answered Jun 15, 2015 at 20:41

Axel Amthor

11.1k1 gold badge28 silver badges45 bronze badges

1 Comment

Tom B. Over a year ago

sorry to be not clear enough, but the price and the status is all in the column "attribute_value" and the entity_id just has a number

Mark B · Accepted Answer · 2015-06-15 21:45:17Z

0

You could use a sub select to get the value like this

SELECT entity AS e,
  (SELECT SUM(value) FROM custom_attribute_values WHERE entity = e AND attribute = 2) AS s
FROM custom_attribute_values
WHERE value = 'Reserviert'

Your table and column names weren't clear so I made some up...

The first SELECT picks all the 'Reserviert' entities then the sub select sums the values associated with each picked entity.

This is the data I used, with names serial, attribute, value, entity, x

1124, 1, Anfrage,      233, 1
1125, 2, 1188,         233, 1
1126, 4, ,             233, 1
1127, 3, 015757817858, 233, 1
1053, 1, Reserviert,   232, 1
1054, 2, 1700,         232, 1
1057, 3, 017697544266, 232, 1
1058, 4, ,             232, 1
1039, 2, 573,          231, 1
1040, 3, 088259216300, 231, 1
1042, 1, Reserviert,   231, 1
1037, 3, 004367684561, 230, 1
1045, 2, 2346.50,      230, 1
1046, 1, Reserviert,   230, 1
1032, 1, Anfrage,      229, 1
1033, 2, 474,          229, 1
1034, 3, ,             229, 1
1027, 1, Anfrage,      228, 1
1029, 3, ,             228, 1
1030, 2, 588.50,       228, 1
1024, 3, 01729843043,  227, 1
1025, 1, Reserviert,   227, 1
1023, 2, 990,          227, 1

edited Jun 15, 2015 at 21:45

answered Jun 15, 2015 at 21:29

Mark B

6826 silver badges11 bronze badges

1 Comment

Tom B. Over a year ago

This solution looks also good, but I'm not able to get it run.

Collectives™ on Stack Overflow

PHP MySQL query (sum from different rows)

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related