count total figure - 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

count total figure

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-12-06 : 04:50:14

I want to count the total figure as below. But when i use COUNT it count but repeat every of each same data.

NAME CATEGORY STATUS
Amy Question Pending
Amy Question Pending
Amy Question New
Amy Enquiry New
Amy Enquiry New

I want to count for this user Amy, Pending status under Question category has 2, New status under Question category has 1 and so on. Is it possible?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 04:52:56

yes. see below


SELECT NAME,STATUS,COUNT(*) AS Cnt
FROM table
WHERE CATEGORY='Question'
GROUP BY NAME,STATUS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-12-06 : 04:58:36

there are more data:

Alan Enquiry Pending
Alan Enquiry Pending
Alan Question New
....
...
...

Can still use that query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 05:00:50

yeah...why not? provided you want only QUestion category counts, it will still hold good

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-12-06 : 05:00:53

select Name, Category, Status, Count(*) as Cnt
from YourTable
where Name = 'Amy'
group by Name, Category, Status

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-12-06 : 05:02:15

Use it without where clause to get all the result for the whole table

No, you're never too old to Yak'n'Roll if you're too young to die.

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-12-06 : 09:21:56

is working fine.
thanks guys..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 10:19:55

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-12-19 : 03:51:44

just curious..
what if i add in 1 more column which is date.

select .... , datetime, Count(*) as Cnt

It will give me the results to count every each datetime.
How can I just ignore the datetime?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 04:32:39

quote:
Originally posted by peace

just curious..
what if i add in 1 more column which is date.

select .... , datetime, Count(*) as Cnt

It will give me the results to count every each datetime.
How can I just ignore the datetime?

it will give you count including datetime
is your requirement to show date values even when ignoring it in group by? That doesnt make sense so far as datetime is also from same table.
Grouping means effectively returning a single record for each unique combination of values so returning date field value without including it in group by means you can effectively return only single value out of each group for the date field,the rule for which you need to determine ie whether its min of date,max of date or random date inside each group.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-12-19 : 04:39:51

I would like to include datetime because I am applying this query to SSRS. The parameter will be startdate and enddate. Thats is the reason I would like to add in the datetime. But it seems like it count the datetime as well. Is there any way I could ignore to count only the datetime?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 04:46:33

quote:
Originally posted by peace

I would like to include datetime because I am applying this query to SSRS. The parameter will be startdate and enddate. Thats is the reason I would like to add in the datetime. But it seems like it count the datetime as well. Is there any way I could ignore to count only the datetime?

why should you include it in group by just because you want it to be used for filtering? its just a mmatter of including it in where condition alone and ignoring in group by

it will be like


SELECT NAME,STATUS,COUNT(*) AS Cnt
FROM table
WHERE CATEGORY='Question'
AND datedield >= @StartDate
AND datedield < @EndDate + 1
GROUP BY NAME,STATUS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-12-19 : 04:51:42

What i meant is i want the table to look like this:

NAME CATEGORY STATUS DATETIME
Amy Question Pending 2011/12/12 15:16:11
Amy Question Pending 2011/12/12 13:12:07
Amy Question New 2011/12/13 15:14:25
Amy Enquiry New 2011/12/17 07:22:36
Amy Enquiry New 2011/12/17 22:34:05

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 05:15:09

how do you think you can group and at the sametime show all the date values? grouping means aggregating ie merging multiple records based on set of values so once you group excluding date field, all the individual date values cant be returned

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 05:16:53

Alternatively, if you want to show individual date values why should you group at all? do you want to show some aggregate values also inline? Perhaps you can show us how your end output should be to give us more details

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-12-19 : 08:54:52

i would like the output to be like this:

NAME CATEGORY STATUS TOTAL DATETIME
Amy Question Pending 2
Amy Question New 1
Amy Enquiry New 2

For the DATETIME column just publish the date.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 10:47:02

quote:
Originally posted by peace

i would like the output to be like this:

NAME CATEGORY STATUS TOTAL DATETIME
Amy Question Pending 2
Amy Question New 1
Amy Enquiry New 2

For the DATETIME column just publish the date.

just publish the date? which date?
as explained earlier, you cant show individual date values. it should be aggregated using any aggregate functions like max(),min().

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-12-20 : 23:41:13

Hmm..im creating a report. This report parameter will be startdate and end date.
User will select the date to see the report.
So im thinking to show more details which the date will appear from the user selection.

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-12-21 : 04:20:51

Or maybe is it possible to use SUM?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-21 : 12:29:32

quote:
Originally posted by peace

Or maybe is it possible to use SUM?

nope
How can you sum up the date ?
Obviously it doesnt make any sense

so if you're showing aggregated data, you need to apply somekind of aggregation also over date values like MAX,MIN if you want to show it alongside. Otherwise Date should also be a part of group which case you'll get summary values split up based on date values also.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-12-21 : 21:09:51

Still couldn't add in the date.

It will count the time as well, so the total surely will be 1 each.

Anybody have any idea?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources