mysql将求和为单独行,如何基于MySQL中的分组求和单个列

I'm having a Two Tables one is Sales and another one is BrandMaster

My Requirement : How to Sum a single Column based on grouping in MySQL

The Structure and Data of Sales Table:

SNo BID Amount

-----------------------------------

101 1 200

102 2 500

103 5 800

104 8 250

105 1 200

106 2 500

107 5 800

108 8 250

The Structure and Data of BrandMaster Table:

BID BrandName

-------------------------

1 Prod#1

2 Prod#2

3 Prod#3

4 Prod#4

5 Prod#5

6 Prod#6

7 Prod#7

8 Prod#8

My Expected Output:

BID SumAmount

-------------------------

1 400

2 500

3 0

4 0

5 0

6 1600

7 0

8 500

The Sales Table contains the BrandID 'BID' and the Sales Amount with Sales ID 'SNo'. I need the Sum of Sales Amount for each Product. Kindly assist me.

解决方案

You can try this

SqlFiddle Demo

SELECT

BrandMaster.BID,

IFNULL(SUM(Sales.Amount),0) AS SumAmount

FROM BrandMaster

LEFT JOIN Sales ON ( BrandMaster.BID=Sales.BID )

GROUP BY

BrandMaster.BID

ORDER BY

BrandMaster.BID,

Sales.SNo