Login or Register to Ask a Question and Join Our Community


Web Development

How to: IFNULL(SELECT ...,0)

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Web Development How to: IFNULL(SELECT ...,0)
# 1  
Old 10-22-2010
How to: IFNULL(SELECT ...,0)
Hi all,

Wondering is this possible:

SELECT id AS cid, name, phone, url, streettype, IFNULL((SELECT ROUND(AVG(stars)*2, 1) / 2 FROM " . $dbconf{'prefix'} . "reviews WHERE location=cid) AS avgstars,0), ...

OR should it be:

SELECT id AS cid, name, phone, url, streettype, (SELECT ROUND(AVG(stars)*2, 1) / 2 FROM " . $dbconf{'prefix'} . "reviews WHERE location=cid) AS IFNULL(avgstars,0), ...

I am fairly sure you can see what I am trying to achieve here.

In other words, two tables, one with id's and another that relates those ID's to star ratings.
I want to extract the AVG(stars) rating AS avgstars to operate on it later, but, if there is no results for the query: ie. no row exists with the "location" specified, can I make the return "avgstars" equal zero rather than simply NULL.

Thanks,
Aaron.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Select command

Hi I'm using the "select" command in the global_env.sh to log in to the application directory. This file is called in .bashrc profile. Sample code: Filename: global_env.sh set -o vi export severname=$(uname -n) printf '%s\n%30s\n%s\n' "***********************" "Welcome to $severname"... (6 Replies)
Discussion started by: cheers799
6 Replies

2. Programming

Socket and select

I have created two sockets and binded both. My requirement is that 2nd socket must send/ recv data only on expiration of timeval(tv). but the 1st socket must keep on send/recv the data without waiting for the 2nd socket completion...... I have posted my code below...... In this code the 2nd... (3 Replies)
Discussion started by: naresh046
3 Replies

3. Boot Loaders

Reboot and Select Proper Boot device or insert Boot media in select Boot device and press a key

Hello, I have kubuntu on my laptop and now I decided to switch to Windows 7. I made the bios settings properly (first choice is boot from cd\vd) but I see the error " reboot and select proper Boot device or insert Boot media in select Boot device and press a key " I have tried CD and... (0 Replies)
Discussion started by: rpf
0 Replies

4. Programming

Problems with select

Is select only supposed to report state changes on an FD's, state, whether or not it had pending input available in the first place? I've got a situation where select() repeatedly reports no FD's ready for stdin when there's lots of data available. And if it only reports changes, how is this... (2 Replies)
Discussion started by: Corona688
2 Replies

5. Shell Programming and Scripting

Select everything before a pattern

Hi I have i doubt, actually i have to select everything before a word(pattern).For that i am using sed i am using the below line of code but it is not working i am getting a blank instead.. sed -n '/regexp/{g;1!p;};h' file1 Can anyone help? Thanks (15 Replies)
Discussion started by: usha rao
15 Replies

6. Shell Programming and Scripting

select a column

I've a file like this: andrea andre@lol.com october antonio@lol.com marco 45247@pop.com kk@pop.com may pollo@lol.com mary mary@lol.com can I select only the column with email adress? can I utilise a filter with @ ? I want obtain this: ... (2 Replies)
Discussion started by: alfreale
2 Replies

7. UNIX for Advanced & Expert Users

Doubt regarding Select()

Please provide the solution for the following scenario: 1) There are two process named as ProcessA and ProcessB 2) ProcessA has opend a named pipe in read mode.This has been made as blocking mode. 3) ProcessB opens this pipe and writes in Blocking mode.So wat happens is even if Process A goes... (2 Replies)
Discussion started by: sunil_ktg
2 Replies

8. Shell Programming and Scripting

How to select Shell

We are using the line #!/usr/bin/ksh in the script, to run the script in ksh. If I have Bash shell and Ksh, how to select the shell to run the script during runtime? Thanks in advace Victor (2 Replies)
Discussion started by: mvictorvijayan
2 Replies

9. IP Networking

select vs poll

Hi, Off late I had been looking at the differences b/w select() & poll() system calls. The requirement is to reduce the overhead, processor power in waiting for the data. In the kind of connections under consideration there would be very frequent data arriving on the sockets, so poll() fares... (12 Replies)
Discussion started by: smanu
12 Replies

10. UNIX for Dummies Questions & Answers

Select ALL in VI Editor

Hi all, How can i select all and copy text in VI Editor. Rakesh Gupta (22 Replies)
Discussion started by: rakish
22 Replies
Login or Register to Ask a Question

LEARN ABOUT CENTOS

select_into

SELECT 
INTO(7)						  PostgreSQL 9.2.7 Documentation					    SELECT INTO(7)

NAME

       SELECT_INTO - define a new table from the results of a query

SYNOPSIS

       [ WITH [ RECURSIVE ] with_query [, ...] ]
       SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
	   * | expression [ [ AS ] output_name ] [, ...]
	   INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table
	   [ FROM from_item [, ...] ]
	   [ WHERE condition ]
	   [ GROUP BY expression [, ...] ]
	   [ HAVING condition [, ...] ]
	   [ WINDOW window_name AS ( window_definition ) [, ...] ]
	   [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
	   [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
	   [ LIMIT { count | ALL } ]
	   [ OFFSET start [ ROW | ROWS ] ]
	   [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
	   [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

DESCRIPTION

       SELECT INTO creates a new table and fills it with data computed by a query. The data is not returned to the client, as it is with a normal
       SELECT. The new table's columns have the names and data types associated with the output columns of the SELECT.

PARAMETERS

       TEMPORARY or TEMP
	   If specified, the table is created as a temporary table. Refer to CREATE TABLE (CREATE_TABLE(7)) for details.

       UNLOGGED
	   If specified, the table is created as an unlogged table. Refer to CREATE TABLE (CREATE_TABLE(7)) for details.

       new_table
	   The name (optionally schema-qualified) of the table to be created.

       All other parameters are described in detail under SELECT(7).

NOTES

       CREATE TABLE AS (CREATE_TABLE_AS(7)) is functionally similar to SELECT INTO.  CREATE TABLE AS is the recommended syntax, since this form of
       SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently. Furthermore, CREATE TABLE AS offers a
       superset of the functionality provided by SELECT INTO.

       Prior to PostgreSQL 8.1, the table created by SELECT INTO included OIDs by default. In PostgreSQL 8.1, this is not the case -- to include
       OIDs in the new table, the default_with_oids configuration variable must be enabled. Alternatively, CREATE TABLE AS can be used with the
       WITH OIDS clause.

EXAMPLES

       Create a new table films_recent consisting of only recent entries from the table films:

	   SELECT * INTO films_recent FROM films WHERE date_prod >= '2002-01-01';

COMPATIBILITY

       The SQL standard uses SELECT INTO to represent selecting values into scalar variables of a host program, rather than creating a new table.
       This indeed is the usage found in ECPG (see Chapter 33, ECPG - Embedded SQL in C, in the documentation) and PL/pgSQL (see Chapter 39,
       PL/pgSQL - SQL Procedural Language, in the documentation). The PostgreSQL usage of SELECT INTO to represent table creation is historical.
       It is best to use CREATE TABLE AS for this purpose in new code.

SEE ALSO

       CREATE TABLE AS (CREATE_TABLE_AS(7))

PostgreSQL 9.2.7						    2014-02-17							    SELECT INTO(7)