percentage of paid sales by year and month

Question

Post reply

percentage of paid sales by year and month

eczmustafaozturk-596402

Valued Member

Points: 66
More actions
January 3, 2008 at 7:03 am

#181725

[font="Courier New"]
Hello,
I have table in MySQL. I think you can help me.
My table named as SalesTable. It has some rows:
TransactionTime: Time of the sale (datetime)
TransactionAmount: Amount of the sale (double)
Deleted: If the sale is deleted, this value is 1; else 0 (integer)
MoneyPaid: If the money paid, this value is 1; else 0 (integer)
I want to see the the total of sales by year and month; and use this statement:
SELECT
YEAR(TransactionTime) AS tYear,
MONTH(TransactionTime) AS tMonth,
COUNT(TransactionAmount) AS tCount,
ROUND(SUM(TransactionAmount) ,2) AS TotalAmount,
ROUND(AVG(TransactionAmount) ,2) AS AverageAmount
FROM
SalesTable
WHERE
deleted='0'
GROUP BY
tYear, tMonth
ORDER BY
tYear, tMonth;
Result:
+------+------+------+-----------+-------------+
| tYear|tMonth|tCount|TotalAmount|AverageAmount|
+------+------+------+-----------+-------------+
| 2006 | 11 | 36 | 821.09 | 22.81 |
| 2006 | 12 | 264 | 5126.50 | 19.42 |
| 2007 | 1 | 1071 | 28392.30 | 26.51 |
| 2007 | 2 | 1070 | 22544.24 | 21.07 |
| 2007 | 3 | 1071 | 19281.90 | 18.00 |
| 2007 | 4 | 983 | 17043.79 | 17.34 |
| 2007 | 5 | 1052 | 19555.52 | 18.59 |
| 2007 | 6 | 1144 | 21772.20 | 19.03 |
| 2007 | 7 | 1583 | 34390.50 | 21.72 |
| 2007 | 8 | 1195 | 48208.95 | 40.34 |
| 2007 | 9 | 745 | 35449.61 | 47.58 |
| 2007 | 10 | 1417 | 45588.94 | 32.17 |
| 2007 | 11 | 1522 | 42140.03 | 27.69 |
| 2007 | 12 | 1752 | 44019.66 | 25.13 |
| 2008 | 1 | 120 | 3816.36 | 31.80 |
+------+------+------+-----------+-------------+
15 rows in set
It works OK.
But I want to add one more column that shows percentage of the paid sales.
I use this statement:
SELECT
YEAR(TransactionTime) AS tYear,
MONTH(TransactionTime) AS tMonth,
COUNT(TransactionAmount) AS tCount,
ROUND(SUM(TransactionAmount) ,2) AS TotalAmount,
ROUND(AVG(TransactionAmount) ,2) AS AverageAmount,
ROUND(PaidSales.tTotal2 ,2) AS PaidAmount,
ROUND(PaidSales.tTotal2/SUM(TransactionAmount) ,2) AS PercentagePaidAmount
FROM
SalesTable
INNER JOIN
(SELECT
SUM(TransactionAmount) AS tTotal2,
YEAR(TransactionTime) AS tYear2,
MONTH(TransactionTime) AS tMonth2
FROM
SalesTable
WHERE
deleted='0'
GROUP BY
tYear2, tMonth2
HAVING
MoneyPaid='1'
) AS PaidSales
ON
(tYear2=YEAR(TransactionTime) and tMonth2=MONTH(TransactionTime))
WHERE
deleted='0'
GROUP BY
tYear, tMonth
ORDER BY
tYear, tMonth;
+------+------+------+-----------+-------------+--------------+----------------------+
| tYear|tMonth|tCount|TotalAmount|AverageAmount| PaidAmount | PercentagePaidAmount |
+------+------+------+-----------+-------------+--------------+----------------------+
| 2006 | 11 | 36 | 821.09 | 22.81 | 821.09 | 1.00 |
| 2006 | 12 | 264 | 5126.50 | 19.42 | 4879.61 | 0.95 |
| 2007 | 1 | 1071 | 28392.30 | 26.51 | 27275.73 | 0.96 |
| 2007 | 2 | 1070 | 22544.24 | 21.07 | 21760.25 | 0.97 |
| 2007 | 3 | 1071 | 19281.90 | 18.00 | 18064.73 | 0.94 |
| 2007 | 4 | 983 | 17043.79 | 17.34 | 16461.51 | 0.97 |
| 2007 | 5 | 1052 | 19555.52 | 18.59 | 18792.20 | 0.96 |
| 2007 | 6 | 1144 | 21772.20 | 19.03 | 20900.23 | 0.96 |
| 2007 | 7 | 1583 | 34390.50 | 21.72 | 32713.86 | 0.95 |
| 2007 | 8 | 1195 | 48208.95 | 40.34 | 46683.40 | 0.97 |
| 2007 | 9 | 745 | 35449.61 | 47.58 | 34103.03 | 0.96 |
| 2007 | 10 | 1417 | 45588.94 | 32.17 | 44770.61 | 0.98 |
| 2007 | 11 | 1522 | 42140.03 | 27.69 | 40909.48 | 0.97 |
| 2007 | 12 | 1752 | 44019.66 | 25.13 | 30058.80 | 0.68 |
+------+------+------+-----------+-------------+--------------+----------------------+
14 rows in set
It seems working OK. But we cannot see the first month of 2008.
Because there is no payment in the first month yet.
"MoneyPaid='1'" expression causes zero record in the inner query, after that
inner join ... on ... matches nothing. So we cannot see 2008-Jan.
Is there anyone has idea about this? How can we see the percentage of 2008-Jan while
there is no payment.
[/font]