how to use group by without duplicating rows

Posted on 2005-04-19
Last Modified: 2010-03-19

Could someone help me with this query,

Temp table :
No.      Code.      Section            Platform                     Carr      Value
---      -----      -------            --------                     ----      -----
1      3873      MT            Video            A      17
2      3873      MT            Video            A      26
3      3873      MT            Video            v      33

Super table :
Service            Code      Station
--------                       ----      --------
ABCD            3873         vi

Now, there are two tables : Temp & Super.
I want my end result to be as below:

super.service      super.code                     Carr            value
-------------      ----------                     ----            -----
ABCD            3873            A            43(17 + 26)
ABCD            3873            V            33

If I use a group by like below, it does not really group the Carr values for 'A'( 17 + 26), because I have other values in the
select like super.service, super.code.. etc..
The select I tried is :
      Select super.service, super.code, temp.carr, temp.value
      from Super
      right join temp at on temp.code = super.code
      where temp.platform like '%' + super.station + '%'

Please assist..
Question by:bikieswim
    LVL 5

    Accepted Solution

        Select super.service, super.code, temp.carr, SUM(temp.value)
         from Super
                inner join temp at on temp.code = super.code
         group by super.service, super.code, temp.carr
    LVL 1

    Expert Comment

    try this

        Select a.service, a.code, b.carr, SUM(b.value)
         from Super a,tmp b
                a.code = b.code
         group by a.service, a.code, b.carr

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now