summation - 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

General SQL Server Forums

New to SQL Server Programming

summation

Author

Topic

overboard22
Starting Member

13 Posts

Posted - 2012-05-15 : 14:22:15

alright so im gonna write down a quick example of my db, i have this table:
KEY CRIME
100101 MURDER
100101 MURDER
100101 MURDER
100102 MURDER FIRST DEGREE
100102 MURDER FIRST DEGREE
100103 MURDER SECOND DEGREE
100103 MURDER SECOND DEGREE
110101 DAMAGES
110101 DAMAGES
110102 GENERAL DAMAGES
110102 GENERAL DAMAGES

now i need to get the sumation of all the crimes that are involved with the range of KEY im selecting, for example the ones involved with MURDER, meaning i need to get the SUM of all of the MURDER, MURDER FIRST DEGREE AND MURDER SECOND DEGREE. I need my total to be 7 for all MURDER CRIME. In case I wanted the DAMAGES total id get 4 as my total.

what i need to do, is to use for example, a KEY between '100101' and '110103' so i can get the sum total of that range of crimes. now the key is varchar so i dont know how to convert them so it accepts the between keyword, or maybe theres another way to do it, im not sure, i just really need some help with this.

thank you!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-15 : 15:26:30

You can still use between if all the keys can be converted to integers. SQL will do an implicit conversion for you. However, I would prefer to use the following:

SELECT
	[key],
	crime
FROM
	YourTable
WHERE
	[key] >= '100101' AND [key] <= '100104';

If you are looking for the count of crimes, may be this?

SELECT
	[key],
	COUNT(DISTINCT crime) AS DistinctCrimes,
	COUNT(crime) AS CrimeCount
FROM
	YourTable
WHERE
	[key] >= '100101' AND [key] <= '100104'
GROUP BY
	[key]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-15 : 15:30:44

Varchar accepts between too.

WHERE [Key] BETWEEN '100101' AND '100104'

N 56°04'39.26"
E 12°55'05.63"

overboard22
Starting Member

13 Posts

Posted - 2012-05-15 : 15:46:29

alright, so with the query sunitabeck posted, im now getting something like this:
KEY CRIME TOTAL
100101 MURDER 3
100102 MURDER FIRST DEGREE 2
100103 MURDER SECOND DEGREE 2

the thing is, i need the total of all of this in one field together, like instead of the results im showing, id need something like:
CRIME TOTAL
MURDER 7

how can i do that? if i try with SUM it tells me u cant sum varchar, thank you so much for your help again!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-15 : 16:08:31

[code]DECLARE @Sample TABLE
(
[Key] CHAR(6) NOT NULL,
Crime VARCHAR(30) NOT NULL
)

INSERT @Sample
VALUES ('100101', 'MURDER'),
('100101', 'MURDER'),
('100101', 'MURDER'),
('100102', 'MURDER FIRST DEGREE'),
('100102', 'MURDER FIRST DEGREE'),
('100103', 'MURDER SECOND DEGREE'),
('100103', 'MURDER SECOND DEGREE'),
('110101', 'DAMAGES'),
('110101', 'DAMAGES'),
('110102', 'GENERAL DAMAGES'),
('110102', 'GENERAL DAMAGES')

-- SwePeso
SELECT f.Crime,
COUNT(*)
FROM @Sample AS s
CROSS APPLY (
VALUES (PARSENAME(REPLACE(Crime, ' ', '.'), 4)),
(PARSENAME(REPLACE(Crime, ' ', '.'), 3)),
(PARSENAME(REPLACE(Crime, ' ', '.'), 2)),
(PARSENAME(REPLACE(Crime, ' ', '.'), 1))
) AS f(Crime)
WHERE s.[Key] BETWEEN '100101' AND '100103'
AND f.Crime > ''
GROUP BY f.Crime
ORDER BY COUNT(*) DESC,
f.Crime[/code]

N 56°04'39.26"
E 12°55'05.63"

overboard22
Starting Member

13 Posts

Posted - 2012-05-15 : 16:31:38

alright im trying that.. i need to do some inner joins in my query tho, how would i go around to adding them there? ^^

thank you so much again for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources