Create a view from several selects

[wesleycrusher]

I tried to do this in a typical

CREATE VIEW <NAME> AS SELECT DISTINCT <FIELDS> FROM <TABLE> WHERE <CONDITION>

but had no success because I need to find the min and max of a certain field. The multiple WHEREs are what throw me off. I came up using multiple select statements then creating a table of the results. Can anyone give me some guidance on this?

SELECT min(use) AS min_gas_use FROM public.fr WHERE furnace_type='gas' AND portable_ind='false';
SELECT max(use) AS max_gas_use FROM public.fr WHERE furnace_type='gas' AND portable_ind='false';
SELECT min(use) AS min_oil_use FROM public.fr WHERE furnace_type='oil' AND portable_ind='false';
SELECT max(use) AS max_oil_use FROM public.fr WHERE furnace_type='oil' AND portable_ind='false';
SELECT min(use) AS min_portable_use FROM public.fr WHERE portable_ind='true';
SELECT max(use) AS max_portable_use FROM public.fr WHERE portable_ind='true';

I would be creating the table after these run. I'm no expert so any help is appreciated. TY!

 

[gwinn7]

Not completely sure what you are wanting to do, but why not merge the SELECTS to include both max and min?

If you are looking to merge the filter conditions, I would take each WHERE condition and "OR" them. Enclose each expression with parenthesis and OR it with the next one.

Example:

SELECT min(use) as min_gas_use, max(use) as max_gas_use
FROM public.fr
WHERE (furnace_type='gas' AND portable_ind='false') OR (portable_ind='true') ...etc

Hopefully, that helps.

Gary
gwinn7
A+, L+, N+, I+