CODEWITHSUNDEEP
mysqlcount
Stefan
Stefan

Reputation: 365

How to calculate the SUM of any number of COUNTS

I'm wondering if it is possible to totalize all products of the amount of scores for each photo multiplied by their score type factor in one SQL query?

Example:

t = total : my aim, the total score for each photo
c_foo = count score type foo = the amount of scores for each photo with the score type name = foo 
c_bar = count score type bar = the amount of scores for each photo with the score type name = bar
m_foo = foo factor
m_bar = bar factor

The number of different score types is arbitrary.

t = c_foo * m_foo + c_bar * m_bar + … + c_last * m_last

My initial idea is to use the following table structure:

So far I have the following query:

SELECT p.id, st.name, st.factor, COUNT(*) AS count
FROM s2p_photo p
LEFT JOIN s2p_score s 
LEFT JOIN s2p_score_type st 
GROUP BY p.id, st.name
ORDER BY p.id ASC

I receive the name, factor and sum of photos but I'm not able to do the math.

I have no idea if UNION is feasible here.

Is my table structure ok? Does anyone of you has a clue?

PS: Sorry I'm not able to post images; please open them manually in your browser :(

Upvotes: 0

Views: 127

Answers (1)

Eugen Rieck
Eugen Rieck

Reputation: 65314

Not sure whether I understand what you mean, but is this what you are looking for?

SELECT
  id,
  SUM(factor*count) AS totalscore
FROM (
    SELECT
      p.id AS id,
      st.factor AS factor,
      COUNT(*) AS count 
    FROM
      s2p_photo p 
      LEFT JOIN s2p_score s 
      LEFT JOIN s2p_score_type st 
    GROUP BY p.id, st.name
    ORDER BY p.id ASC
) AS baseview
GROUP BY id

Upvotes: 2

Related Questions