Sql - problem with group by

Question

0.00/5 (No votes)

See more:

Hi everyone, Now I try to select the table, below is now my current select table

Source          total   Local     Overseas     Singapore
ALIBABA           1       0           1            0
ALIBABA           1       0           0            1
BBB               1       1           0            0
GOOGLE ADS        1       1           0            0

Now my problem is how to make "ALIBABA" group together and the result i want is as below
Source          total   Local     Overseas     Singapore
ALIBABA           2       0           1            1
BBB               1       1           0            0
GOOGLE ADS        1       1           0            0

What I have tried:

select gt2.Source,count(Region) as total,
CASE
	WHEN Region = 'MALAYSIA' THEN count(Region)
	ELSE 0
END as LOCAL,
CASE
	WHEN Region = 'OVERSEAS' THEN count(Region)
	ELSE 0
END as OVERSEAS,
CASE
	WHEN Region = 'SINGAPORE' THEN count(Region)
	ELSE 0
END as SINGAPORE
from [ERP_GT].[dbo].[GTMAX_RefTable] as gt1
left join [ERP_GT].[dbo].[GTMAX_Customer] as gt2 on gt2.Region = gt1.Name
where Type='reg' and Created between '2020-05-05' and '2020-05-08'
group by gt2.Source, Region

Posted 21-Jul-20 17:11pm

gan chee siang

Updated 21-Jul-20 20:37pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2020-07-21T20:22:00

Solution 1

Try this:

SQL

USE [ERP_GT];

SELECT [OVERSEAS], [MALAYSIA], [SINGAPORE], [OVERSEAS] + [MALAYSIA] + [SINGAPORE] AS Total
FROM 
(
    SELECT gt2.Source, Region, Local
    FROM [GTMAX_RefTable] as gt1
        LEFT JOIN [GTMAX_Customer] as gt2 on gt2.Region = gt1.Name
    WHERE Type='reg' and Created BETWEEN '2020-05-05' and '2020-05-08'

) AS DT
PIVOT(COUNT(*) FOR Region IN ([OVERSEAS], [MALAYSIA], [SINGAPORE])) AS PT

For further details. please see: Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs[^]

Posted 21-Jul-20 20:22pm

Maciej Los

Comments

gan chee siang 22-Jul-20 2:40am

Solve already and thx u so much

Maciej Los 22-Jul-20 3:46am

You're very welcome.

Garth J Lancaster 22-Jul-20 4:31am

well done Maciej :-)

Maciej Los 22-Jul-20 6:57am

Thank you, Garth.

Sandeep Mewara 22-Jul-20 6:52am

Nice +5

Catching up after long time! :) How are you.

Maciej Los 22-Jul-20 6:57am

Thank you, Sandeep.
Yes, i haven't seen you on CP very long time.
Well... i'm fine. And how are you? Seems, you've been so busy...

Sandeep Mewara 22-Jul-20 7:02am

Yeah, was in middle of quite a few things. Trying to find some time now. :)