Trying to add counts (totals) from another table

[svivian]

I have two tables with the format:

 

page: id / url

error: id / type / pageid / message

 

error.pageid relates to page.id, so there are multiple 'error' tuples for each page

error.type can be 1 (error) or 2 (warning).

 

I want to display a list of pages with an error count and a warning count. I've managed to get the total errors+warnings with this query:

SELECT p.id, p.url, count(e.id) AS tot
FROM page p, error e
WHERE e.pageid=p.id
GROUP BY p.id

 

However I can't find a way to split the total into the two types. I tried this but it takes ages and gives really large numbers (bit in [ ] I've tried with and without):

SELECT p.id, p.url, count(e.id) AS errors, count(w.id) AS warnings
FROM page p, error e, error w
WHERE e.pageid=p.id AND w.pageid=p.id [AND e.pageid=w.pageid]
GROUP BY p.id

 

Maybe I'm missing an extra join or something? Can anyone help?

[fenway]

That won't work... you'll end up with too many rows.  It's probably just easier to use subqueries to generate a pivot table.

[GingerRobot]

Can't you just return two rows, one of which contains the number of errors and one of which contains the number of warnings?

 

SELECT page.id,type,COUNT(error.type) as count FROM page,error WHERE page.id=1 GROUP BY error.type

[svivian]

Well I can just do two queries, one that gets the errors and one that gets the warnings.

 

However, I have another problem now - anything where there are 0 errors doesn't show up. From a quick bit of searching apparently LEFT JOIN is the way to go in this circumstance, to make sure I get all the pages listed... but I've tried and it makes no difference.

 

Do I have this correct? This query is still getting the total errors+warnings for now:

SELECT p.id, p.url, count(e.id) AS errors
FROM page p
LEFT JOIN error e ON (e.pageid=p.id)
GROUP BY p.id

 

EDIT: scratch that, the above query works fine, but adding

WHERE e.type=1

to the query gets rid of the 0 entries again...

[fenway]

You need to move e.type = 1 to the ON clause.