Adding where clause to this SQL statement

Question

Hi, I asked a question earlier this week and i got this statement as the answer:

select published, count(*) nbr
from table1
group by published
order by nbr desc
limit 1

I now want to know how it is possible to add a where clause in to the statement so that i can limit the results i get to the different types of publications. My table looks like this:

id

type

Title

published

The where clause will be on the type column so for example where type=3.
Thanks in Advance
Dean

Answer 1

It's time to start learning some basic SQL, don't you think? :)

http://dev.mysql.com/doc/refman/4.1/en/select.html

SELECT
[ALL | DISTINCT | DISTINCTROW ]
  [HIGH_PRIORITY]
  [STRAIGHT_JOIN]
  [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
  [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
  [ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
  [ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name' export_options
  | INTO DUMPFILE 'file_name'
  | INTO @var_name [, @var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]

Answer 2

Sure, just add the WHERE statement after your FROM statement, and before your GROUP BY statement.

Answer 3

select published, count(*) nbr    
from table1
where type = 3
group by published
order by nbr desc
limit 1

Answer 4

See, as far the basic rules are concerned, where clause puts constraint on every row. Row level constraint first creates a table on which the Grouping is made. So Group comes after Where. If you want group level constraint, you might go for Having clause, but remember one thing, you can apply having constraint only to items that are common in group(in your case its published).

As already stated, Where clause in your case will be put just before "group by " and after "from table1"