[solved]SQL query-Counting frequency in each route

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
silverme
Forum Newbie
Posts: 13
Joined: Tue Sep 27, 2005 4:24 pm

[solved]SQL query-Counting frequency in each route

Post by silverme »

I'm trying to set up an query in MS Access.

table tbDELIVERY has field ORIGIN and DESTINATION (both in char)

ORIGIN DESTINATION
1 A
1 B
1 B
1 B
1 A
1 A
2 A
2 B
1 B
2 B
1 A
2 A
1 C
--------------------------------------------

How do I set up a query in SQL that counts the frequency of each route (1A, 1B, 1C, 2A,2B)?

1A --> 4
1B --> 4
1C --> 1
2A --> 2
2B --> 2

Thanks a lot!
Last edited by silverme on Tue Apr 15, 2008 2:50 pm, edited 3 times in total.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: SQL query? Counting frequency in each route

Post by Christopher »

Something like:

Code: Select all

SELECT ORIGIN, DESTINATION, SUM(ORIGIN) FROM tbDELIVERY GROUP BY ORIGIN, DESTINATION;
(#10850)
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: SQL query? Counting frequency in each route

Post by EverLearning »

SUM(origin) wont work. You need COUNT

Code: Select all

SELECT ORIGIN, DESTINATION, COUNT(ORIGIN) FROM tbDELIVERY GROUP BY ORIGIN, DESTINATION;
silverme
Forum Newbie
Posts: 13
Joined: Tue Sep 27, 2005 4:24 pm

Re: [Solved]SQL query? Counting frequency in each route

Post by silverme »

Thanks guys
shamuraq
Forum Newbie
Posts: 1
Joined: Wed Apr 09, 2008 9:50 pm

Re: [Solved]SQL query? Counting frequency in each route

Post by shamuraq »

now my problem is almost like tt. let me just paint a scenario. in mysql i have a table with test-dates as the rows(y-axis) and the classes i am teaching as the columns(x-Axis). The data in the table are scores from my students from all these classes. how do i "count" the total number of students(classes combined) lets say scoring 50%? really appreciate any help to shed some light in this matter.

the difference with the previous problem is tt it is only focused on values within one column whereas mine is COUNTING number of similar result from multiple columns.
Post Reply