Looking for a good horror game

Deadly Premonition is quite a good horror game and it's 50% off right now.

outlast ?

SCP-087 (it's free)

I was not really scared by the game, but others players seemed to get a thrill out of it.

Fear(the first fear, not 2 or 3). It's so motherfucking scary, so many jumpscares.

If you like point'n'click games and oldies, I remember Phantasmagoria as an evil little dark thing.

I know you aren't really into shooters (as posted above) but I think many will agree L4D2 is an experience any zombie fans should go through. :P

I think you are looking along the lines of Amnesia/Outlast.

I honestly love CONDEMNED CRIMINAL ORIGINS.
For me one of the scariest game there is, but tastes differ.

Alpha Protocol. Horrible bugs.

cry of fear

Condemned 1, Dead space, S.T.A.L.K.E.R. Shadow of Chernobyl, Outlast

Penumbra, Amnesia, Cryostasis, Cold Fear, Silent Hill, Obscure, Condemned
Maybe?

cpdamned criminal origin, amensia dark descent, the suffering 1 and 2.. Saw the game..

Another one to wait for if you have Crysis. The Triptych mod for Crysis. Looks amazing, and a very lovecraftian fully custom horror story. Reeks of atmosphere( in a good way). Got a very amnesia-esque feel from the demo I played. Apparently comin out this year Christmas.

Alone in the Dark, Cold Fear & first Dead Space :3 (but with headphones) , SCP - Containment , CLive Barker's Jericho&Undying, Condemned 2.
Also u can check out System Shock (but it's kinda hard to say that it's horror with it's graph :D) & Fatal Frame (camera Obscura)

Also, Alan Wake & for sure The Suffering o_o

I know you stated you're not into shooters, but at stated above, Dead Space is one of my favorite horror games. There's not a HUGE deal of shooting, except in certain parts, but it's all about atmosphere and subtle sounds. Dead Space is one of a few games out there I'd fully recommend.

Dude... Outlast is the game for you. For real.

Lone Survivor is exactly what you're describing and it's very good.

The Walking Dead (Telltale). It doesn't have the highest replay-value and it is more apocalyptic than horror. But it doesn't rely on jumpscares and it is a great game.
Alan Wake also seems to be what you want, although I haven't played much of it, so I can't comment on its quality.

Cold Fear looks good, check it out.

other than that...the most obvious ones.
Dead Space
Outlast
Huntsman: the Orphanage
Amnesia
Cryostasis

on Alan Wake, it's not a horror game but IT'S A MUST PLAY.

Doom 3, bricks shall be shat.

I can recommend 'The Cat Lady' though it's not really horror, but dark and psychological like you want it to be and it has a nice story.
Also you may be able to trade it for rather cheap, because it was in an old Groupees bundle ;)

Silent Hill.

AudioSurf with Lady Gaga song.