Write a big sql query or handle it through code?

Question

I have 2 tables built in this way:

Trips
- id
- organization_id REQUIRED
- collaboration_organization_id OPTIONAL
...other useless fields...

Organizations
- id
- name REQUIRED
...other useless fields...

Now I have been asked to create this type of report:

I want the sum of all trips for each organization, considering that if they have a collaboration_organization_id it should count as 0.5, obviusly the organization in collaboration_organization_id get a +0.5 too

So whenever I have a trip that has organization_id AND collaboration_organization_id set, that trip count as 0.5 for both organizations. If instead only organization_id is set, it counts as 1.

Now my question is composed by two parts:

1.

Is a good idea to "solve" the problem all in SQL?

I already know how to solve it through code, my idea is currently "select all trips (only those 3 fields) and start counting in ruby". Please consider that I'm using ruby on rails so could still be a good reason to say "no because it will work only on mysql".

2.

If point 1 is YES, I have no idea how to count for 0.5 each trip where it's required, because count is a "throw-in-and-do-it" function

it's a good idea to do as much in the DB as possible. count() can be faked with sum(if(...your credit logic here...)) as well. — Marc B
– Marc B, Commented Jan 16, 2013 at 20:33

Mr. Radical · Accepted Answer · 2013-01-18 11:30:23Z

2

I'm not familiar with ruby on rails, but this is how you can do this with MySQL.

Sample data:

CREATE TABLE Trips(
  id int not null primary key,
  organization_id int not null,
  collaboration_organization_id int null
  );

INSERT INTO Trips (id,organization_id,collaboration_organization_id)
VALUES
(1,1,5),
(2,1,1),
(3,1,2),
(4,11,1),
(5,1,null),
(6,2,null),
(7,10,null),
(8,6,2),
(9,1,3),
(10,1,4);

MySQL Query:

SELECT organization_id,
sum(CASE WHEN collaboration_organization_id IS null THEN 1 ELSE 0.5 End) AS number
FROM Trips
GROUP BY organization_id;

Try it out via: http://www.sqlfiddle.com/#!2/1b01d/107

EDIT: adding collaboration organization

Sample data:

  CREATE TABLE Trips(
  id int not null primary key,
  organization_id int not null,
  collaboration_organization_id int null
  );

INSERT INTO Trips (id,organization_id,collaboration_organization_id)
VALUES
(1,1,5),
(2,1,1),
(3,1,2),
(4,11,1),
(5,1,null),
(6,2,null),
(7,10,null),
(8,6,2),
(9,1,3),
(10,1,4);


CREATE TABLE Organizations(
  id int auto_increment primary key,
  name varchar(30)
  );

INSERT INTO Organizations (name)
VALUES
("Org1"),
("Org2"),
("Org3"),
("Org4"),
("Org5"),
("Org6"),
("Org7"),
("Org8"),
("Org9"),
("Org10"),
("Org11"),
("Org12"),
("Org13"),
("Org14"),
("Org15"),
("Org16");

MySQL query:

SELECT O.id, O.name,
sum(CASE WHEN T.collaboration_organization_id IS null THEN 1 ELSE 0.5 End) AS number
FROM Organizations AS O LEFT JOIN Trips AS T  
ON T.organization_id = O.id OR T.collaboration_organization_id = O.id
WHERE T.collaboration_organization_id = O.id OR O.id = T.organization_id
GROUP BY O.id;

http://www.sqlfiddle.com/#!2/ee557/15

edited Jan 18, 2013 at 11:30

answered Jan 16, 2013 at 23:01

Mr. Radical

1,8381 gold badge20 silver badges30 bronze badges

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

6 Comments

Francesco Belladonna Over a year ago

SQL Fiddle... Didn't know about it, cool. However, your answer is good but the main point now is another: what if an organization does only trips in "collaboration", so it never appears as organization_id but only as collaboration_organization_id. I should still find the sum for that organization, with this query won't appear.

Mr. Radical Over a year ago

@Fire-Dragon-DoL Agree SQL Fiddle is a great why to easily show how something works. Back to the topic. How will you deal with trips with more than 2 organizations? I would suggest to move the collaboration_organization_id and organization_id to an new table called trip_participants which contains all organizations that will be part of the trip.

Mr. Radical Over a year ago

@Fire-Dragon-DoL I have added the collaborating organizations to the query see the edit.

Francesco Belladonna Over a year ago

Thanks a lot, I've never done such a query, O.o And I didn't split them to "table_partecipants" due to the fact that they specified they will have a maximum of 2 partecipants (the main is forced, the second is optional). Thanks a lot anyway, very detailed answer.

Mr. Radical Over a year ago

I had never done a query like this before either, but I like a challenge. :-) The best of luck with your project.

|

Collectives™ on Stack Overflow

Write a big sql query or handle it through code?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related