CODEWITHSUNDEEP
mysqlsql
Alexis_user
Alexis_user

Reputation: 425

Condition on another field into a sum

I have 2 tables ticket and article, I have this query :

SELECT hour(ticket.stats_iso) hour_num, 
       IF(MINUTE(ticket.stats_iso) < 30, 0, 1) interval_num, 
       min(ticket.stats_iso) interval_begin, 
       max(ticket.stats_iso) interval_end, 
       count(ticket.id) countid, 
       sum(ticket.montantTTC) sum_subtotal, 
       (sum(ticket.montantTTC)/count(ticket.id)) as moy
FROM ticket
WHERE ticket.annule = 0
      AND ticket.stats_iso
          BETWEEN '2012-01-01 00:00:00' AND '2012-01-01 23:59:59'
GROUP BY hour_num, interval_num
ORDER BY hour_num, interval_num

I have statistics about number of tickets and the sum of montantTTC.

I would like to have the number of articles then I joined :

SELECT hour(ticket.stats_iso) hour_num, 
       IF(MINUTE(ticket.stats_iso) < 30, 0, 1) interval_num, 
       min(ticket.stats_iso) interval_begin, 
       max(ticket.stats_iso) interval_end, 
       count(DISTINCT ticket.id) countid, 
       sum(ticket.montantTTC) sum_subtotal, 
       (sum(ticket.montantTTC)/count(ticket.id)) as moy, count(article.id)
FROM ticket, article
WHERE ticket.annule = 0
      AND ticket.stats_iso
          BETWEEN '2012-01-01 00:00:00' AND '2012-01-01 23:59:59'
      AND ticket.uid = article.uid_ticket
GROUP BY hour_num, interval_num
ORDER BY hour_num, interval_num

It works BUT sum(ticket.montantTTC) sum_subtotal becomes false... (indeed, with the join it sums many duplicate ticket because of 1 ticket has many articles

count(ticket.id) countid has a problem too but I can write count(DISTINCT ticket.id) countid.

Does it exist a way to make a condition on sum(ticket.montantTTC) sum_subtotal ?

Example (of course, doesn't work) : sum(if ticket.id IS DISTINCT then ticket.montantTTC) sum_subtotal

Example

Thank you very much.

Upvotes: 0

Views: 54

Answers (1)

Arth
Arth

Reputation: 13110

   SELECT HOUR(t.stats_iso) hour_num, 
          ROUND(MINUTE(t.stats_iso)/60) interval_num, 
          MIN(t.stats_iso) interval_begin, 
          MAX(t.stats_iso) interval_end, 
          COUNT(t.id) countid, 
          SUM(t.montantTTC) sum_subtotal, 
          SUM(t.montantTTC)/count(t.id) moy, 
          SUM(COALESCE(a.article_count,0)) article_count
     FROM ticket t
LEFT JOIN (
       SELECT uid_ticket, COUNT(*) article_count
         FROM article 
     GROUP BY uid_ticket 
          ) a
       ON t.uid = a.uid_ticket
    WHERE t.annule = 0
      AND t.stats_iso BETWEEN '2012-01-01' AND '2012-01-02'
 GROUP BY hour_num, interval_num
 ORDER BY hour_num, interval_num

Upvotes: 1

Related Questions