How to consolidate the count?

Reg code Cntry code Area code Count
--- ---- --- --
AF AO CAB 15
AF AO LAD 20
AF BF OUA 23
AF BI BJM 11
AF BW GBE 72
AF CD FIH 30
AF CD MNB 8

I need the result like this

Reg code cntry code Total

AF AO 35
AF BF 23
AF BI 11
AF Bw 72
AF CD 38

consolidated total of area code 15+20 = 35, 30 + 8 = 38

table structures(two tables I am using)

create temp table country (ccode char(5),
acode char(4), rcode char(4));

create temp table report(count char(5),acode char(5));

how to write the sql?This is a simple sum and group by:

select c.rcode, c.ccode, sum(t.count)
from country c, temp t
where c.acode = t.acode
group by c.rcode, c.ccode;

BTW, why is column count declared as char(5)?

How to consolidate multiple rows into a single column

Hello,
I would like some help on developing a SQL query.
I have a Team table and a Person Table. For simplicity sake, lets say
the Team has a key and team name. The Person has a person key, team
key, and person name.
I want to query for all team members, and store the results in a single
column. So, the resulting view would have three columns: team key, team
name, and a list of all people on the team.
Any pointers or tips appreciated,
J Wolfgang GoerlichSELECT T.team_key, T.team_name, P.person_name
FROM Team AS T
JOIN Person AS P
ON T.team_key = P.team_key ;
David Portas
SQL Server MVP
--|||Here is a sample:
=====
CREATE TABLE Team
(
TeamID INT,
TeamName VARCHAR(20)
)
GO
CREATE TABLE Person
(
PersonID INT,
TeamID INT,
PersonName VARCHAR(50)
)
GO
INSERT INTO Team VALUES (1, 'Development')
INSERT INTO Team VALUES (2, 'Release')
INSERT INTO Person VALUES (1, 1, 'Bob')
INSERT INTO Person VALUES (2, 1, 'Mason')
INSERT INTO Person VALUES (3, 2, 'Chris')
INSERT INTO Person VALUES (4, 2, 'Scott')
INSERT INTO Person VALUES (5, 2, 'Bruce')
GO
=====
The above just creates some sample tables and data. Now, we can define a
function that concatenates the list of team members as follows:
=====
IF (OBJECT_ID ('dbo.formTeamList') IS NOT NULL)
DROP FUNCTION dbo.formTeamList
GO
CREATE FUNCTION dbo.formTeamList (@.teamID INT)
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @.teamList VARCHAR(8000); SET @.teamList = ''
SELECT
@.teamList = @.teamList + ', ' + ISNULL (PersonName, '')
FROM
Person
WHERE
TeamID = @.teamID
RETURN (STUFF (@.teamList, 1, 2, ''))
END
GO
=====
Once done, we can test this out as follows:
=====
SELECT
TeamID, TeamName, dbo.formTeamList (TeamID)
FROM
Team
=====
Although the method shown above works, it is not recommended as there are
limitations:
(1) The function is called for every row in the team table. This can cause
performance problems for large lists.
(2) The function can only concatenate a list of 8000 characters in length
(4000 if you are using UniCode).
Such logic is usually best handled in the application tier of your program,
sinnce you have good flexibility to rotate rows into columns.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
<jwgoerlich@.gmail.com> wrote in message
news:1131967790.112616.168370@.g44g2000cwa.googlegroups.com...
> Hello,
> I would like some help on developing a SQL query.
> I have a Team table and a Person Table. For simplicity sake, lets say
> the Team has a key and team name. The Person has a person key, team
> key, and person name.
> I want to query for all team members, and store the results in a single
> column. So, the resulting view would have three columns: team key, team
> name, and a list of all people on the team.
> Any pointers or tips appreciated,
> J Wolfgang Goerlich
>

How to consolidate duplicate records

Hello,
I'm assigned a task to clean up a table which has the data like below:
col1--col2--col3--col4
103 20 606 $50
103 20 606 $60
I was told to consolidate the sales$ and only keep one record, the primary
key is on col1,col2 and col3.
How can I do this?
Thanks,
SarahWhat about
SELECT col1,col2,col3,sum(col4) as sales
From YourTable
Group by col1,col2,col3
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"SG" <sguo@.coopervision.ca> schrieb im Newsbeitrag
news:%23DMa7GoRFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm assigned a task to clean up a table which has the data like below:
> col1--col2--col3--col4
> 103 20 606 $50
> 103 20 606 $60
> I was told to consolidate the sales$ and only keep one record, the primary
> key is on col1,col2 and col3.
> How can I do this?
> Thanks,
> Sarah
>|||You can use GROUP BY like:
SELECT col1, col2, col3, SUM( col4 ) AS "col4"
FROM tbl
GROUP BY col1, col2, col3 ;
Anith|||Thanks Anith and Jens for your quick response. How could I forget this? I
made a mistake to group by sales column which I should use sum()so it gave
me the wrong result.
Thanks again,
Sarah
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eChkdLoRFHA.2252@.TK2MSFTNGP15.phx.gbl...
> You can use GROUP BY like:
> SELECT col1, col2, col3, SUM( col4 ) AS "col4"
> FROM tbl
> GROUP BY col1, col2, col3 ;
> --
> Anith
>