• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

how to use group by without duplicating rows

Hi,

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



Synopsis:
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..
Thankyou,
A.
0
bikieswim
Asked:
bikieswim
1 Solution
 
obahatCommented:
    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
0
 
AmiiitCommented:
try this

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now