CODEWITHSUNDEEP
mysqlsql
sineverba
sineverba

Reputation: 5172

MySQL Query with several "for each"

I have this table:

idTransactions  idCampaignsList idMemberCard    amountOriginal  amountFinal   dateTransaction
1   2   1   50.00   100.00          2012-10-31 12:45:41
2   3   1   0.00    -50.00          2012-10-31 12:47:25
3   2   2   255.00  255.00          2012-10-31 17:19:07
4   1   2   95.00   95.00           2012-11-02 20:38:36
5   3   2   0.00    -400.00         2012-11-02 20:39:50
24  1   4   10.00   2.00            2012-11-03 11:16:3

With this query

SELECT  SUM(amountOriginal) AS euro,
SUM(amountFinal) AS deducted,
EXTRACT(YEAR_MONTH FROM(dateTransaction)) AS period
FROM transactions 
INNER JOIN campaignsList ON campaignsList.idCampaignsList = transactions.idCampaignsList 
INNER JOIN customers ON customers.idCustomer = campaignsList.idCustomer 
WHERE   customers.idCustomer = 14
        AND
        transactions.idCampaignsList = 2
GROUP BY period
ORDER BY period

I obtain this result

euro             deducted   period
305.00           305.00         201210
14860.46            -22758.50   201211
1845.00         -34710.00   201212

For last 12 month, sum of "charged" and discharged.

Now, idCampaignsList could be 1, 2, also 500, it depend on how many "campaigns" have my idCustomer (retrieved via JOIN).

I'd like have a query dinamic, that, "for each" idCampaignsList, print me sum of amountOriginal and amountFinal.

To intend, from previos table, i would like to have

idCampaignsList    SUM(amountOriginal)   SUM(amountFinal)  period
1                  50                    50                201210
2                  255                   255               201210
2                  95                    -305              201211
4                  10                    2                 201211

So, for every period, sum columns for every distinct idCampaignsList, where idCampaignsList is dinamically (SELECT idCampaignsList FROM myOtherTable where idCustomer = 14)

Upvotes: 1

Views: 61

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79939

I'd like have a query dinamic, that, "for each" idCampaignsList, print me sum of amountOriginal and amountFinal.

I think the several For each that you mean, is a GROUP BY transactions.idCampaignsList.

Try to add the transactions.idCampaignsList to the SELECT list, remove the predicate transactions.idCampaignsList = 2 from the WHERE clause and list that column in the GROUP BY clause as well, like so:

SELECT  
  transactions.idCampaignsList
  SUM(amountOriginal) AS euro,
  SUM(amountFinal) AS deducted,
  EXTRACT(YEAR_MONTH FROM(dateTransaction)) AS period
FROM transactions 
INNER JOIN campaignsList
        ON campaignsList.idCampaignsList = transactions.idCampaignsList 
INNER JOIN customers 
        ON customers.idCustomer = campaignsList.idCustomer 
WHERE customers.idCustomer = 14
GROUP BY period, transactions.idCampaignsList
ORDER BY period

Upvotes: 1

Related Questions