• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

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  
0
rfinaly
Asked:
rfinaly
  • 4
  • 3
  • 3
  • +1
2 Solutions
 
SuperdaveCommented:
You could combine them something like this (untested):

SELECT COUNT(field2) as ADTotal,AddedOn,
  (SELECT COUNT(field1) FROM Applications AS A2 WHERE A1.AddedOn=A2.AddedOn) AS Total FROM Applications AS A1;
0
 
ThomasianCommented:
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?
0
 
SuperdaveCommented:
I meant to put a GROUP BY on that, like this:

SELECT COUNT(field2) as ADTotal,AddedOn,
  (SELECT COUNT(field1) FROM Applications AS A2 WHERE A1.AddedOn=A2.AddedOn) AS Total FROM Applications AS A1 GROUP BY AddedOn;
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Muhammad Ahmad ImranDatabase DeveloperCommented:
well, better is, provide some data and required results.
0
 
rfinalyAuthor Commented:
Attached is an excel file with fields and results set, hope that helps.
Thank you in advance
Roy
Example.xlsx
0
 
ThomasianCommented:

DECLARE @t table (AddedOn datetime, Admit bit)

INSERT INTO @t
SELECT '11/1/2010', 1
UNION ALL SELECT '11/1/2010', 1
UNION ALL SELECT '11/12/2010', 0
UNION ALL SELECT '11/12/2010', 1
UNION ALL SELECT '11/12/2010', 0
UNION ALL SELECT '11/12/2010', 1

SELECT COUNT(1) 'Total Records Per Day'
      ,COUNT(CASE WHEN Admit=1 THEN 1 END) 'Total Admits'
      ,AddedOn
FROM @t
GROUP BY AddedOn

Open in new window

0
 
rfinalyAuthor Commented:
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
0
 
rfinalyAuthor Commented:
Thomas: Do I need the insert statment to run your query?
Thank you
Roy
0
 
ThomasianCommented:
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.
0
 
SuperdaveCommented:
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?
0
 
rfinalyAuthor Commented:
Thank you very much, both solutions works very well.
I appriciate your time.
Roy
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now