i have table which contains eight columns
In all eight columns there are two values either "Personal" and "Professional"
I want to create query where i can count only personal from all eight column
how do i write query for that
thank you
If you want records where each column is set to 'Personal' then : - SELECT Count(*)
-
FROM [myTable]
-
WHERE ([Col1]='Personal')
-
AND ([Col2]='Personal')
-
AND ([Col3]='Personal')
-
AND ([Col4]='Personal')
-
AND ([Col5]='Personal')
-
AND ([Col6]='Personal')
-
AND ([Col7]='Personal')
-
AND ([Col8]='Personal')
If you want records where any one of the columns is set to 'Personal' then : - SELECT Count(*)
-
FROM [myTable]
-
WHERE ('Personal' In([Col1],[Col2],[Col3],[Col4],[Col5],[Col6],[Col7],[Col8]))
It may be a good idea at this point to clarify exactly what it is that you do want.
6 14002
Try - SELECT Count(*)
-
FROM myTable
-
WHERE Col1="Personal" OR Col2="Personal" OR...Col8="Personal"
change table and field names to suit
NeoPa 32,556
Expert Mod 16PB
If you want records where each column is set to 'Personal' then : - SELECT Count(*)
-
FROM [myTable]
-
WHERE ([Col1]='Personal')
-
AND ([Col2]='Personal')
-
AND ([Col3]='Personal')
-
AND ([Col4]='Personal')
-
AND ([Col5]='Personal')
-
AND ([Col6]='Personal')
-
AND ([Col7]='Personal')
-
AND ([Col8]='Personal')
If you want records where any one of the columns is set to 'Personal' then : - SELECT Count(*)
-
FROM [myTable]
-
WHERE ('Personal' In([Col1],[Col2],[Col3],[Col4],[Col5],[Col6],[Col7],[Col8]))
It may be a good idea at this point to clarify exactly what it is that you do want.
And if you want a count of each row for each colum that has personal, then use a sum of an IIf or Switch function.
Column Names are item 1,item 2 till item 8
In these column there are two entries either personal or behaviour i want to count only personal from these columns
You can count all fields in one count. It depends on your requirements and what they contain.
In my count version 1 you will find that The Paid Field contains a total of 15, The Use Field contains a total of 23 out of 26 records.
I use similar counts like this routinely.
In count version 2 I Included both fields to Count twice and Both as Group By to see the individual Break Downs and how they add up to their Totals. Once again the numbers differentiate.
So yes it is entirely possible depending on what you have and what you want to do.
As I did read the post, seeing as how that would be required to answer on topic, it lacks complete information. Given that, I made a guess as to what the OP wanted.
Thanks
Jeff Jones
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Regnab |
last post by:
I have a table called PropertyCode with 4 properties as fields, with a
list of paddocks under each. I would like to use these as a parameter
in my query, where any records which have a paddock...
|
by: simchajoy2000 |
last post by:
Hi,
I am trying to use a VB.NET listview object to display information
from a datatable. I need to have two columns of information but I
don't want the user to be able to select each column...
|
by: ShastriX |
last post by:
Getting a weird error while trying out a query from Access 2003 on a
SQL Server 2005 table.
Want to compute the amount of leave taken by an emp during the year.
Since an emp might be off for...
|
by: John Devlon |
last post by:
Hi,
Does anyone know how to get a value of a second column of a selected item
in Listview.
I've create a listview and added this code
Listview.Items.Clear()
Listview.Columns.Clear()...
|
by: kudzu |
last post by:
I'm new to SQL Server 2005 and have a question.
I have a table with the following format:
Columns:
ID Col_A Col_B Col_C Col_D .... (Col_X)
Values:
1 Yes ...
|
by: TB |
last post by:
Hi there all,
How do i count , using a query, the number of records in a certain
table, where one of the fields has a null value, in other words only
the records where the field has a null...
|
by: Soniad |
last post by:
Hello,
In stored procedure, i am using query that fetches 2 columns record but single row. i have used cursor to fetch single record.
is there any way to fetch the record and put it in...
|
by: Pramod Tripathi |
last post by:
Hi
I want to make an expression to find out the value of a field particular employee of previous month, and i have put the following expression for it
prevdays:
DLookUp("","","='" & ! & "'" And...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |