如何基于MySQL中的分组求和单个列 [英] How to Sum a single Column based on grouping in MySQL

问题描述

我有两个表，一个是销售"，另一个是"BrandMaster"

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

我的要求:如何基于MySQL中的分组对单个列求和

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

销售表的结构和数据:

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

BrandMaster表的结构和数据:

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

我的预期输出:

BID           SumAmount          
-------------------------
1             400
2             500
3             0
4             0
5             0
6             1600
7             0
8             500

销售表"包含BrandID'BID'和带有销售ID'SNo'的销售额.我需要每个产品的销售金额总和.请帮助我.

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.

推荐答案

您可以尝试一下 SqlFiddle演示

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

这篇关于如何基于MySQL中的分组求和单个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！