Group By Clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Group By Clause

Author

Topic

jsc0624
Starting Member

13 Posts

Posted - 2008-05-10 : 04:17:00

Hi all,

Got a problem here, I am not quite familiar yet with the sql group by clause. As an illustration, I have a table with column StoreName and Sales.

StoreName Sales
DFA_Main 50
DFA_Main 50
DFA_Branch 60
DFA_Branch 60
DFA_OtherBranch 10
MMDA_Main 50
MMDA_Main 50
MMDA_Branch 30
MMDA_Branch 30

In my understanding if we are going to group this table by StoreName the result will be:

StoreName Sales
DFA_Main 100
DFA_Branch 120
DFA_OtherBranch 10
MMDA_Main 100
MMDA_Branch 60

But what would be the sql statement to produce an output like this:

StoreName Sales
DFA 230
MMDA 160

Is it possible for me to do that? Thanks for the help in advance.

===============
JSC0624
===============

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-10 : 04:25:21

Try like this:-

SELECT t.StoreName,SUM(t.Sales) AS Sales
FROM
(
SELECT CASE WHEN CHARINDEX('_',StoreName) > 0 
            THEN  LEFT(StoreName ,CHARINDEX('_',StoreName)-1)
       ELSE StoreName
       END AS StoreName,
       Sales
FROM YoutTable)t
GROUP BY t.StoreName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources