CODEWITHSUNDEEP
mysqlsql
Jeff Gladnick
Jeff Gladnick

Reputation: 634

Grouping MySQL query by date range and productId

So I am trying to alter my sql code (see below for screenshot of current results + sql) to group the data by the month AND sum up all the paymentSplitAmounts. Each row should be a unique productId

So the end result would be something like

    productID        total        month
    1                500          11-2011
    2                650          11-2011
    3                250          11-2011
    1                100          10-2011
    2                150          10-2011
    3                750          10-2011

I can't seem to get the syntax right. Where am I going wrong?

https://i.sstatic.net/p8xzP.jpg

    select  
        cpd.paymentId, cpd.paymentId, cpd.productId, cpd.paymentSplitAmount, cp.campaignId, cp.paymentDate 
    from campaign_payment_detail cpd 
    inner join 
        campaign_payment cp on cp.paymentId = cpd.paymentId 
    inner join product on cpd.productId = product.productId 
    where 
    1=1 
    and cp.campaignId = 2413

Upvotes: 0

Views: 174

Answers (3)

GolezTrol
GolezTrol

Reputation: 116140

You already mentioned 'grouping'. For that you need to add group by to your query, to group the data by productid and month, then you can add sum to sum the paymentSplitAmount.

The grouping syntax isn't wrong, it is missing completely. :)

select  
    cpd.productId, sum(cpd.paymentSplitAmount) as total, date_format(cp.paymentDate, '%m-%Y')
from campaign_payment_detail cpd 
inner join campaign_payment cp on cp.paymentId = cpd.paymentId 
inner join product on cpd.productId = product.productId 
where 
  cp.campaignId = 2413
group by cp.productId, date_format(cp.paymentDate, '%m-%Y')
order by date_format(cp.paymentDate, '%m-%Y') desc, cp.productId

This assumes cp.paymentDate already contains the months. If not, you will have to round each date to the first of the month and group by that.

Now groups by month.

Upvotes: 0

MoshiBin
MoshiBin

Reputation: 3196

Looks like you want to group then sort your results:

SELECT cpd.productId, SUM(cpd.paymentSplitAmount), DATE_FORMAT(cp.paymentDate, '%b-%Y')
  FROM campaign_payment_detail cpd
  JOIN campaign_payment cp ON cp.paymentId = cpd.paymentId
  JOIN product ON cpd.productId = product.productId
 WHERE cp.campaignId = 2413
 GROUP BY cpd.productId, DATE_FORMAT(cp.paymentDate, '%b-%Y')
 ORDER BY cp.paymentDate DESC, cpd.productId ASC

edit: Using DATE_FORMAT to format the date like you want.

Upvotes: 1

Marcx
Marcx

Reputation: 6836

First, based on the query you provided and without other information, the table product is useless..

I will do that:

select  
        cpd.paymentId,
        SUM(cpd.paymentSplitAmount) as total, 
        cp.campaignId, 
        cp.paymentDate 
    from campaign_payment_detail cpd 
    inner join 
        campaign_payment cp on cp.paymentId = cpd.paymentId 
    where 
    cp.campaignId = 2413
GROUP BY cpd.productId, cp.paymentDate
ORDER BY cpd.paymentId ASC, cp.paymentDate DESC

Upvotes: 1

Related Questions