Sql with group by and count

Question

I have an sql select query that has a group by. Group by doesn't work. I want to count all the records after the group by statement. Is there a way for this directly from sql? For example, I want to count mobtel and the total sum of amount.

SELECT
    --DISTINCT 
    B.ABS_CHANNEL_NAME AS AGENCY,
    B.ABS_REGION AS REGION,
    A.MSISDN AS [Mobtel],
    A.Activation_date AS [Activation Date],
    LEFT(B.DATE, 8) AS [Top Up Date],
    DATEDIFF(d, CONVERT(char(10), A.Activation_date,121), CONVERT(char(10), B.DATE,121)) AS [Days Elapsed],
    B.Amount,
    C.DSM as [DSM],
    C.Channel as [CHANNEL]
FROM [ODS_BI_R].[dbo].[R_Activation] A
JOIN [ODS_Raw].[dbo].[D_TopUpTransaction_Amax] B
ON A.MSISDN = CONCAT('63', B.B_NUM) 
JOIN [dbo].[Retailer_Sims] C 
on B.A_NUM = C.RETAILERID
WHERE Activation_date LIKE '%201701%'
AND B.AMOUNT <> '0:00'
AND A.SEGMENTATION = 'Prepaid'
AND CONVERT(INT,AMOUNT) >= 20
AND DATEDIFF(d, CONVERT(char(15), A.Activation_date,121), CONVERT(char(15), B.DATE,121)) BETWEEN 0 AND 30
---Group BY AGENCY, DSM, Channel, count(A.MSISDN), sum(B.AMOUNT)
ORDER BY A.Activation_date, A.MSISDN, LEFT(B.DATE, 8);

Why do a GROUP BY when no aggregate functions (e.g. MAX, SUM, COUNT) are used? Also the general GROUP BY rule says: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function! — jarlh, Mar 27 '17 at 06:49
This looks to be the SQL Server dialect of SQL and not Oracle. I'm changing the tags. If this change is incorrect please say so. — Ben, Mar 27 '17 at 07:54
You don't understand what `GROUP BY` actually does. With `GROUP BY AGENCY, DSM, Channel` you'd say "give me one result row per AGENCY, DSM, and Channel, which may be what you want. `GROUP BY ..., count(A.MSISDN), sum(B.AMOUNT)` however doesn't make sense, as the count and the sum are *results*. You'd put them in the `SELECT` clause hence. Moreover you'd remove things from your `SELECT` clause, such as `B.Amount`, because there can be many amounts per group, so you can only show an aggregation, such as `SUM(B.Amount)`. — Thorsten Kettner, Mar 27 '17 at 08:08
With such questions you should always show some sample data and expected results. — Thorsten Kettner, Mar 27 '17 at 08:09

score 0 · Accepted Answer · answered Mar 27 '17 at 02:37

0

SELECT
    AGENCY
    , DSM
    , Channel
    , count(A.MSISDN)
    , sum(B.AMOUNT)
FROM [ODS_BI_R].[dbo].[R_Activation]            A
JOIN [ODS_Raw].[dbo].[D_TopUpTransaction_Amax]  B ON A.MSISDN = CONCAT('63', B.B_NUM) 
JOIN [dbo].[Retailer_Sims]                      C on B.A_NUM = C.RETAILERID
WHERE 
    Activation_date LIKE '%201701%'
    AND B.AMOUNT <> '0:00'
    AND A.SEGMENTATION = 'Prepaid'
    AND CONVERT(INT,AMOUNT) >= 20
    AND DATEDIFF(d, CONVERT(char(15), A.Activation_date,121), CONVERT(char(15), B.DATE,121)) BETWEEN 0 AND 30
Group BY 
    AGENCY
    , DSM
    , Channel

answered Mar 27 '17 at 02:37

user1327961

452
2
8

Got this errorMsg 209, Level 16, State 1, Line 23 Ambiguous column name 'Channel'. Msg 209, Level 16, State 1, Line 8 Ambiguous column name 'Channel'. – Yinah Mar 27 '17 at 03:26
@Yinah which table has channel? r_activation, d_topuptransaction_amax, retailer_sims? whichever it is, we'd need to prefix the channel field with the alias. – user1327961 Mar 27 '17 at 17:48

Sql with group by and count

1 Answers1