Hello,
I need to write a SELECT statement that will display the most popular
categories.
This means I need a 'category count' for each of the messages in the
messages table, and I don't know how to do this.
Here is the structure of the 2 tables:
messages table
id, title, message, category, thread, status, date_posted
categories table
id, title, sub_cat
I've tried the following:
select categories.id, categories.title, count(messages.category) as
count from messages, categories group by categories.title;
but 'count' only returns the total count of messages with a category
entry, and I need the number of times each category id is entered in
the messages table.
Any help is greatly appreciated. Also, does anyone have any online
references for learning more complex SQL statements?
Thanks,
Aaron 3 2519
use group by messages.category instead of categories.title
select c.id, c.title, count(m.category) as cnt from messages m
left join categories c
on c.id=m.category
group by m.category
order by cnt desc
acorn71 íàïèñàâ:
Hello,
I need to write a SELECT statement that will display the most popular
categories.
This means I need a 'category count' for each of the messages in the
messages table, and I don't know how to do this.
Here is the structure of the 2 tables:
messages table
id, title, message, category, thread, status, date_posted
categories table
id, title, sub_cat
I've tried the following:
select categories.id, categories.title, count(messages.category) as
count from messages, categories group by categories.title;
but 'count' only returns the total count of messages with a category
entry, and I need the number of times each category id is entered in
the messages table.
Any help is greatly appreciated. Also, does anyone have any online
references for learning more complex SQL statements?
Thanks,
Aaron
que wrote:
use group by messages.category instead of categories.title
select c.id, c.title, count(m.category) as cnt from messages m
left join categories c
on c.id=m.category
group by m.category
order by cnt desc
Thank you for your response, que. That statement worked great!
Does anyone have any suggestions for me as to online resources where I
can learn how to execute the more challenging SQL queries. I find the
manual at mysql.com very useful, but sometimes I don't know where to
begin to look, and I just want to write the most effecient SQL commands
so my code is clean and I can minimize server-side logic.
Thanks,
Aaron
acorn71 wrote:
[snipped]
begin to look, and I just want to write the most effecient SQL commands
so my code is clean and I can minimize server-side logic.
It is always more efficient to only return data that the client actually needs
rather than return a lot that gets tossed (processed). Let the database engine
do what it is designed to do... manage your data and you will do well.
>
Thanks,
Aaron
--
Michael Austin.
Database Consultant This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: JP Belanger |
last post by:
I have a question on select count(), which may betray my lack of
database knowledge. Here goes.
I have java code driving a transaction which goes like this:
select count(*) from table
...
|
by: Joseph Shraibman |
last post by:
On a 7.3.4 database:
explain analyse select count(*) from elog;
Aggregate (cost=223764.05..223764.05 rows=1 width=0) (actual
time=81372.11..81372.11 rows=1 loops=1)
-> Seq Scan on elog ...
|
by: Cro |
last post by:
Hello Access Developers,
I'd like to know if it is possible to perform a count in an expression
that defines a control source.
My report is based on a query. In my report, I want a text box to...
|
by: Henk Ernst Blok |
last post by:
Hi Posgres users/developers,
Can anyone explain why PosgreSQL (version 7.4.5 on Linux) does a full
table scan to compute a count(*) on a base table after a vacuum analyze
has been done with no...
|
by: DAnne |
last post by:
I'm trying to do a simple count function in xslt and it's turning out to be extremely painful.
I have a report that brings back a set of responses for each question per section in an Audit.
I...
| |
by: heckstein |
last post by:
I am working in Access 2002 and trying to create a report from our company's learming management system. I am not a DBA and most of my SQL knowledge has been self taught through trial and error. I...
|
by: carolyns |
last post by:
I am working with Access 2000, on Windows XP (all updates).
I designed a survey in Word with 150+ questions. Roughly 60 questions use a lookup list of 7 different responses.
I have the survey...
|
by: petter |
last post by:
Hi!
I have two questions: one question that regards the COUNT-function, and one about how to display a month even if I don’t have any data for that month.
I have an Access database where I want...
|
by: weirdguy |
last post by:
Hello all,
Before you read my question, I would advise you to visit the Image-Link.
The image-link shows 4Tables - its field, Primary, Foreign Keys, Relationships. The bottom of the Image, it...
|
by: jaxkookie |
last post by:
I have been trying to count each answer for each question and roll this up to one line item per question with totals for each answer. what I am getting is staggered answers. example, question 1 has 9...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |