homepage Welcome to WebmasterWorld Guest from 54.205.119.163
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MySQL - multiple counts
Trying to use two seperate count functions in a single query
AstonJay32




msg:1580426
 6:20 pm on Sep 22, 2005 (gmt 0)

SCHEMA:

category
========
catID - auto, primary
catName - varchar

entry
=====
entryID - auto, primary
catID - int, foreign
memberID - int
dateEntered - date/time

evaluated
=========
entryID - foreign
judgeID - foreign

------------------------------------------
I'd like to list all the catagory names and next to each name list the number of judged entries and total entries.

Example-
CategoryName Judged Total
--------------------------------
Abstract 2 / 4
Sculptures 0 / 2
Clay 3 / 7

I've been able to successfully get the query to work for either the Judged or Total but not both. Here's the query to get the total:

SELECT DISTINCT category.catID, category.catName, (SELECT COUNT(*) FROM entry WHERE entry.catID = category.catID) AS Total_Entries FROM category, entry
ORDER BY category.catID;

This works no problem. The query for the judged works too:

SELECT DISTINCT entry.entryID, (select count(*) from eval where eval.entryID = entry.entryID) AS Judged_Entries FROM entry, eval ORDER BY entry.entryID;

But when I try both.....

SELECT DISTINCT category.catID, category.catName, (select count(*) from entry where entry.catID = category.catID) AS Total_Entries, (select count(*) from eval where eval.entryID=entry.entryID) AS JudgedEntries
FROM category, entry, eval ORDER BY category.catID;

.... it's taking the max number of judged entries and listing a seperate record for each consecutive number up to the max, like this.

Category Name Judged / Total
--------------------------------
Abstract 0 / 4
Abstract 1 / 4
Abstract 2 / 4
Sculptures 0 / 2
Sculptures 1 / 2
Sculptures 2 / 2
Clay 0 / 7
Clay 1 / 7
Clay 2 / 7

If the highest number of judged was 7, each category would have 7 consecutive records (0 through 6). I know I got to use some form of JOIN, but I'm still a bit new to this and I'm not sure exactly what to do.

Any help or advice is greatly appreciated.

Thanks

 

R e b r a n d t




msg:1580427
 8:39 am on Sep 23, 2005 (gmt 0)

try:
SELECT
categoryName,
total,
judged
FROM
category INNER JOIN (
SELECT
catID,
COUNT(entry.entryID) as total
FROM
category INNER JOIN entry ON category.catID = entry.catID
GROUP BY catID
) cat_tot ON category.catID = cat_tot.catID
INNER JOIN (
SELECT
catID,
COUNT(entry.entryID) as judged
FROM
category INNER JOIN entry ON category.catID = entry.catID
INNER JOIN evaluated ON entry.entryID = evaluated.entryID
GROUP BY catID
) cat_jud ON category.catID = cat_jud.catID

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved