MULTIPLI FILED COUNT - 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

MULTIPLI FILED COUNT

Author

Topic

Nasser
Starting Member

12 Posts

Posted - 2008-04-20 : 12:33:10

HELLO FRIENDS

HOW YOU DOING ? HOPE FINE AND HAPPY

I BIG PROBLEM WHICH IS SMALL FOR YOU

HERE IS MY SQL STATMENT

SQLString = "select F01,F02,F03,F04,F05,F06,F07 from MovementTable where f011= KLM and f06 between 2 and 4"

HOW CAN HAVE COUNT IF F02=3 , F03=4 ,F04=5 THE TOTAL SHOULD BE =12

THANKS IN ADVANCE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-20 : 13:30:43

Not quite clear. Do you want to display a total row for above conditions? just include conditions under WHERE and take COUNT(Field) in select. If not, please elaborate on what you are looking at?

Nasser
Starting Member

12 Posts

Posted - 2008-04-20 : 13:44:24

hello there

thanks for helping me

my code should be look like this
F02 = 3
f03 = 3
f04 = 3

Select count(F02,F03,F04)as total f01,F05,F06,F07

from MovementTable

where f011= KLM and f06 between 2 and 4"

total should be = 9

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-20 : 13:49:13

Are these counts of fields or sum? Are you talking about individual counts and total?

Nasser
Starting Member

12 Posts

Posted - 2008-04-20 : 14:02:16

the database contain table its name is MovementTable

the table contain fileds F01,F02,F03,F04,F05,F06

the fileds contain
F01=KLM
F02=3
F03=5
F04=6
F05=747
F06=INDIA

SELECT *,COUNT OR SUM FROM MovementTable WHERE F01 = KLM

I WANT TO HAVE FILED IN REPORTS CONTAIN THE SUM OF F02,F03,F04

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-20 : 15:12:17

[code]SELECT *, F02 + F03 + F04 as Total FROM MovementTable WHERE F01 = 'KLM'[/code]

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Nasser
Starting Member

12 Posts

Posted - 2008-04-20 : 16:28:32

sorry mr RyanRandall

your code will not give me the total number like = 3+5+6 =14

it will show me 356

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-20 : 18:23:48

Your columns must be varchars rather than ints. That's not ideal and you should consider changing them - otherwise you need to convert them to integers before adding them...

SELECT *, cast(F02 as int) + cast(F03 as int) + cast(F04 as int) as Total FROM MovementTable WHERE F01 = 'KLM'

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Nasser
Starting Member

12 Posts

Posted - 2008-04-21 : 02:34:52

mr .RyanRandall

i'm using Microsoft Access database

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-21 : 04:42:24

Then you're in the wrong forum. Try this one...

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-21 : 05:31:59

quote:
Originally posted by RyanRandall

Your columns must be varchars rather than ints. That's not ideal and you should consider changing them - otherwise you need to convert them to integers before adding them...

SELECT *, cast(F02 as int) + cast(F03 as int) + cast(F04 as int) as Total FROM MovementTable WHERE F01 = 'KLM'

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

or

SELECT *, F02*1 + F03*1 + F04*1 as Total FROM MovementTable WHERE F01 = 'KLM'

Madhivanan

Failing to plan is Planning to fail

Nasser
Starting Member

12 Posts

Posted - 2008-04-21 : 10:37:03

mr madhivanan and mrRyanRandall

thanks for helping me , i found the problem

the data type of filed is text , it should be Number so that sql statement work fine

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-22 : 02:54:12

quote:
Originally posted by Nasser

mr madhivanan and mrRyanRandall

thanks for helping me , i found the problem

the data type of filed is text , it should be Number so that sql statement work fine

Always use proper datatype to store data

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources