rfinaly
asked on
SQL Query COUNT function
Hello Exports,
I need some help in trying to run a query on our SQL server that will count several fields. I tried to do this in simple statment but got no where, so I created the following stored procedure which get me the correct count but the the software I created that for cannot recognize stored procedures.
Is there any other way to do the below in a SELECT query?
SELECT COUNT (field1) AS Total, AddedOn FROM applications Group By AddedOn
Select COUNT (field2) As ADTotal FROM Applications where field2 = 1 GROUP BY AddedOn, Field2
What I am trying to do is to count several fields within the same table and group them by date received so the results will be something like
Date Total ADTotals
09/01 2 4
09/01 1 8
09/02 5 30
I need to count total of six fields in the same table.
Thank you
Roy
I need some help in trying to run a query on our SQL server that will count several fields. I tried to do this in simple statment but got no where, so I created the following stored procedure which get me the correct count but the the software I created that for cannot recognize stored procedures.
Is there any other way to do the below in a SELECT query?
SELECT COUNT (field1) AS Total, AddedOn FROM applications Group By AddedOn
Select COUNT (field2) As ADTotal FROM Applications where field2 = 1 GROUP BY AddedOn, Field2
What I am trying to do is to count several fields within the same table and group them by date received so the results will be something like
Date Total ADTotals
09/01 2 4
09/01 1 8
09/02 5 30
I need to count total of six fields in the same table.
Thank you
Roy
If you are grouping by the date, why is there 2 records with the same date in your sample output?
Do you want to count distinct (number of unique) values for each field?
Can you provide some sample data and the expected output?
Do you want to count distinct (number of unique) values for each field?
Can you provide some sample data and the expected output?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
well, better is, provide some data and required results.
ASKER
Attached is an excel file with fields and results set, hope that helps.
Thank you in advance
Roy
Example.xlsx
Thank you in advance
Roy
Example.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SuperDave your query works if I made those changes.
SELECT COUNT(field2) as ADTotal,AddedOn,
(SELECT COUNT(field1) FROM Applications AS A2 WHERE A1.AddedOn=A2.AddedOn AND field2=1) AS Total FROM Applications AS A1 GROUP BY AddedOn;
Take a look at the Excel I submitted and see if we are on the right track.
Thank you very much
SELECT COUNT(field2) as ADTotal,AddedOn,
(SELECT COUNT(field1) FROM Applications AS A2 WHERE A1.AddedOn=A2.AddedOn AND field2=1) AS Total FROM Applications AS A1 GROUP BY AddedOn;
Take a look at the Excel I submitted and see if we are on the right track.
Thank you very much
ASKER
Thomas: Do I need the insert statment to run your query?
Thank you
Roy
Thank you
Roy
No, line 1-9 are just code to test the query.
You only need code from line 11-15 and change @t to your actual table name.
You only need code from line 11-15 and change @t to your actual table name.
The query looks right (like what I meant). I don't have a way of easily interpreting weird xml formats so I can't look at it, but what if anything more do you want done with the query?
ASKER
Thank you very much, both solutions works very well.
I appriciate your time.
Roy
I appriciate your time.
Roy
SELECT COUNT(field2) as ADTotal,AddedOn,
(SELECT COUNT(field1) FROM Applications AS A2 WHERE A1.AddedOn=A2.AddedOn) AS Total FROM Applications AS A1;