count & sum

Question

Post reply

count & sum

da-473995

SSC Enthusiast

Points: 181
More actions
August 14, 2007 at 1:42 am

#103399

sorry im SQL beginner
ID_NRID_NR_pKZ
11v
12x
13v
21x
22x
23v
i want to count nr of KZ of which KZ='v'
i just can make a table like
ID_NR #(KZ=v)
1 2
2 1
any help?

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Hi There,

Try this.

SELECT ID_NR,COUNT(*)

FROM

WHERE KZ='z'

GROUP BY ID_NR

Thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

da-473995 SSC Enthusiast Points: 181 More actions · Answer 2

thanxx

i can do that:-P

but what i want is the sum over all nr of id_nr which kz='V'

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

I'm confused.,

What should your result look like?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

da-473995 SSC Enthusiast Points: 181 More actions · Answer 4

da-473995

SSC Enthusiast

Points: 181

August 14, 2007 at 1:58 am

#726680

count(nr)

3

in this example

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

what about this:

SELECT COUNT(*)

FROM

WHERE KZ='z'

taht will give you a result of 3

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

da-473995 SSC Enthusiast Points: 181 More actions · Answer 6

yes.. but the point is it could be duplicated:

ID_NR ID_NR_p KZ

1 1 v

1 2 x

1 3 v

2 1 x

2 2 x

2 3 v

and the result should only be 3

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 7

Hi There,

Will this do the trick?

SELECT COUNT(*)

FROM (SELECT DISTINCT ID_NR,ID_NR_p,KZ FROM dbo.Table WHERE KZ ='V') [nest]

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

steve smith-401573 SSChampion Points: 10889 More actions · Answer 8

da -

since you are a self-admitted beginner, here's another technique that you may want to try here: use the search feature of this site - enter your keyword phrase and select only the forums (discussions?) option to search through. It will give you a varied selection of threads that may or may not be on topic for you and may be an efficient way for you to learn about a specific topic quickly.

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 9

Also, check out GROUP BY and Aggregate Functions in Books Online. You may also want to try the free tutorial at W3Schools.com... won't teach you the specifics of SQL Server but will allow you to learn the basics very quickly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

da-473995 SSC Enthusiast Points: 181 More actions · Answer 10

da-473995

SSC Enthusiast

Points: 181

August 16, 2007 at 1:38 pm

#727477

perfect tnax loads guys

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 11

SELECT ID_NR,

SUM(CASE WHEN KZ = 'v' THEN 1 ELSE 0 END) AS [#]

FROM Table1

GROUP BY ID_NR

ORDER BY ID_NR

N 56°04'39.16"
E 12°55'05.25"

da-473995 SSC Enthusiast Points: 181 More actions · Answer 12

hello peter

i was thinging abt this solution(but still need to sum up for all distinct id!) but i have asround 2 millions id a´t least:-P

the only thing i need is count(*) as description before!

thanx anyway

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 13

SELECT ID_NR, SUM([#]) AS [#] FROM (

SELECT DISTINCT ID_NR, ID_NR_p, CASE WHEN KZ = 'v' THEN 1 ELSE 0 END AS [#]

FROM Table1) AS d

GROUP BY ID_NR

ORDER BY ID_NR

N 56°04'39.16"
E 12°55'05.25"

da-473995 SSC Enthusiast Points: 181 More actions · Answer 14

i actually check this solution.. it did not work:-(