| Author |
 Topic  |
|
|
mrdatabase
Starting Member
United Kingdom
12 Posts |
 Posted - 04/28/2012 : 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
India
227 Posts |
Posted - 04/28/2012 : 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
United Kingdom
12 Posts |
Posted - 04/28/2012 : 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
India
227 Posts |
Posted - 04/28/2012 : 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
India
227 Posts |
Posted - 04/28/2012 : 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
United Kingdom
12 Posts |
Posted - 04/28/2012 : 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)
Singapore
16746 Posts |
Posted - 04/28/2012 : 09:34:50
|
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);
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
Time is always against us
|
Edited by - khtan on 04/28/2012 09:37:45 |
|
|
|
mrdatabase
Starting Member
United Kingdom
12 Posts |
Posted - 04/28/2012 : 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)
Singapore
16746 Posts |
Posted - 04/28/2012 : 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
Time is always against us
|
Edited by - khtan on 04/28/2012 09:45:25 |
|
|
|
vinu.vijayan
Posting Yak Master
India
227 Posts |
Posted - 04/30/2012 : 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" |
|
|
| |
Topic |
|
Display Results From A Count (*) As a %
