Display Results From A Count (*) As a %

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

Display Results From A Count (*) As a %

Author

Topic

mrdatabase
Starting Member

12 Posts

Posted - 2012-04-28 : 05:17:08

Hi everyone, i am brand new to sql and i am using microsoft access SQL for coding. I have developed a query the only problem is i want the query to display my results as a percentage. Could anybody point me in the right direction?

SQL Currently is:

SELECT Weekday(date_time) AS Weekday, Count (*) AS Amount_Of_Usage
FROM activity_usage
WHERE date_time BETWEEN #JAN-01-2012# AND #MAR-01-2012#
AND Activity_code = "A002"
GROUP BY WEEKDAY(date_time);

Thanks

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-28 : 05:45:16

If you just want the Percentage sign(%) with the Count then you can change your code:


SELECT Weekday(date_time) AS Weekday, Count (*) AS Amount_Of_Usage 
FROM activity_usage
WHERE date_time BETWEEN #JAN-01-2012# AND #MAR-01-2012#
AND Activity_code = "A002"
GROUP BY WEEKDAY(date_time);

to the following code:


SELECT Weekday(date_time) AS Weekday, Cast(COUNT(*) As Varchar)+'%' AS Amount_Of_Usage 
FROM activity_usage
WHERE date_time BETWEEN #JAN-01-2012# AND #MAR-01-2012#
AND Activity_code = "A002"
GROUP BY WEEKDAY(date_time);

Hope this helps you.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

mrdatabase
Starting Member

12 Posts

Posted - 2012-04-28 : 06:13:40

Hi thankyou for your response! I entered the code however i am getting a syntax error with "Cast(COUNT(*) As Varchar)+'%'".

Would you happen to know what it is?

Currently my results look like this from the old SQL

Weekday Amount of usage
1 5
2 9
3 6
4 6
5 6
6 5
7 5

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-28 : 08:13:07

quote:
Originally posted by mrdatabase

Hi thankyou for your response! I entered the code however i am getting a syntax error with "Cast(COUNT(*) As Varchar)+'%'".

Would you happen to know what it is?

Currently my results look like this from the old SQL

Weekday Amount of usage
1 5
2 9
3 6
4 6
5 6
6 5
7 5

Based upon the sample data you provided I altered the Query a little and tested it on the sample data and it is working fine. Following is the working query.


--Creating Table

Create Table Ex
(Weekdayy int,
 Amount_of_usage int)

--Inserting sample data

Insert Into Ex
Select 1, 5
Union ALL
Select 2, 9
Union ALL
Select 3, 6
Union ALL
Select 4, 6
Union ALL
Select 5, 6
Union ALL
Select 6, 5
Union ALL
Select 7, 5

--Altered Query

SELECT Weekday, Cast(COUNT(*) As Varchar)+'%' AS Amount_Of_Usage 
FROM Ex
--WHERE date_time BETWEEN #JAN-01-2012# AND #MAR-01-2012#
--AND Activity_code = "A002"
GROUP BY WEEKDAY;

I had to comment out the Where part because the sample data you provided does not have any fields for dates, neither does it have an Activity_code field.

Other than that this query doesn't give any error.

One more thing is that I really don't understand your business requirement behind this query. What are you actually trying to do???....The result set returns 1 as count for each weekday. What is the point??

N 28° 33' 11.93148"
E 77° 14' 33.66384"

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-28 : 08:24:49

What is the Error that you are getting while running the query?

N 28° 33' 11.93148"
E 77° 14' 33.66384"

mrdatabase
Starting Member

12 Posts

Posted - 2012-04-28 : 09:11:11

The theory behind the query is that i have a table called activity usage which records the datetime when a customer uses an activtiy.

So the table design is Activity_Usage [Customer_ID#, Activity Code#, Date_Time]

I need to get a query running to display the results of the activity usage per day. ( example monday has 6%, tuesday has 10%...) so far i have counted the amount an activity has been entered on a certain day (example monday (which is displayed as 2 i think in the database results) equals 5 and so on) however i wish to turn this count into a percentage.

Thats the problem i am having

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-28 : 09:34:50

[code]
SELECT Weekday(date_time) AS Weekday, Count (*) * 100.0 / count(*) over() AS Amount_Of_Usage
FROM activity_usage
WHERE date_time BETWEEN #JAN-01-2012# AND #MAR-01-2012#
AND Activity_code = "A002"
GROUP BY WEEKDAY(date_time);
[/code]

and display that "%" in your front end application

just noticed that you are using MS Access. The query that i posted might not work in MS Access. It is for SQL Server

KH
[spoiler]Time is always against us[/spoiler]

mrdatabase
Starting Member

12 Posts

Posted - 2012-04-28 : 09:37:29

Still an error, with the "Count (*) * 100.0 / count(*) over()" of the function.

What does the over() function do?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-28 : 09:43:46

quote:
Originally posted by mrdatabase

Still an error, with the "Count (*) * 100.0 / count(*) over()" of the function.

What does the over() function do?

Thanks

Sorry, didn't realized that you are using MS Access as you have posted under "New to SQL Server Programming". I don't think the over() is available under MS Access. And sorry i can't help you with MS Access as that is not my forte

there is a MS Access forum here http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3 You should post your questions on Access over there

KH
[spoiler]Time is always against us[/spoiler]

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-30 : 00:18:33

I agree with Khtan....You'll get better solutions at the Ms Access Forum. You should post there.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources