Member Avatar for deolalkar_pooja

Hi to all,
I created following table
---------------------------------------
crate table publisher(name varchar(20), city varchar(20))
---------------------------------------

I want to run following query.
'list all the positions of charcter 'a' from name.'

for this, i ran following query,

----------------------------------------

select patindex('%a%', name)
from publisher;

---------------------------------------

But, it doesn't show proper output. When i put 'city' column instead of 'name' column, it shows right output. What is the problem?

=======================================

Also, I want to dispaly the name of publishers whose getting minimum and maximum profit. for this, i ran following query,

--------------------------------------

select name, max(profit), min(profit) from publisher;

-------------------------------------

It shows the error like that "'name' is not part of aggregate function". what should i do for retrieving name of publishers who getting minimum and maximum profit.

Thanks & regards,
Pooja.

Edited by deolalkar_pooja because: n/a
  • 2 Contributors
  • 2 Replies
  • 325 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by deolalkar_pooja
Member Avatar for smantscheff

patindex() is not a mysql function. You cannot use it in a select clause unless you define the function first.
To select all names like "%a%", use the like operator:

SELECT * FROM publisher where name like "%a%";

To use aggregate functions like sum() or avg() you need an GROUP BY aggregate clause:

select name, max(profit), min(profit) from publisher GROUP BY name;

To find which publisher has the maximum profit, use

SELECT name from publisher order by profit desc limit 1;
Edited by smantscheff because: n/a
Member Avatar for deolalkar_pooja

hi smantscheff, i will work on ur suggestion abt 1st query.

I tried the same also abt 2nd query, but it shows same error. can u give different solution?

Thanks & regards.
Pooja.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.