Home » SQL & PL/SQL » SQL & PL/SQL » Select 3 different 'WHERE's from same table.
Select 3 different 'WHERE's from same table. [message #200041] Fri, 27 October 2006 11:14 Go to next message
spohlso
Messages: 19
Registered: April 2006
Junior Member
I tried searching and couldn't find this. At any rate, I have a table that looks roughly like this:

TYPE COLOR SIZE WEIGHT
_A____Red___S___10_
_B____Red___L___23_
_A____BLUE__L___11_
_C____Red___S___92_
_C____Red___L___10_
_C____GREEN_L___17_

I have a query like this
SELECT COUNT(Table.COLOR) as ColorA
WHERE Table.TYPE = 'A'

With a result of
ColorA
__2__

I need all three though. I need a query that will give me a result that looks like:

ColorA ColorB ColorC
__2____1_____3__

Any ideas?
Re: Select 3 different 'WHERE's from same table. [message #200042 is a reply to message #200041] Fri, 27 October 2006 11:30 Go to previous messageGo to next message
gururajster
Messages: 26
Registered: October 2006
Location: bangalorre
Junior Member

select
(SELECT COUNT(Table.COLOR) WHERE Table.TYPE = 'A') ColorA,
(SELECT COUNT(Table.COLOR) WHERE Table.TYPE = 'B') ColorB,
(SELECT COUNT(Table.COLOR) WHERE Table.TYPE = 'C') ColorC
from Dual;



[Updated on: Fri, 27 October 2006 11:31]

Report message to a moderator

Re: Select 3 different 'WHERE's from same table. [message #200043 is a reply to message #200042] Fri, 27 October 2006 11:36 Go to previous messageGo to next message
spohlso
Messages: 19
Registered: April 2006
Junior Member
Thanks!
Re: Select 3 different 'WHERE's from same table. [message #200060 is a reply to message #200043] Fri, 27 October 2006 13:35 Go to previous messageGo to next message
spohlso
Messages: 19
Registered: April 2006
Junior Member
I tried it and it worked perfectly for a stand alone query. I tried using it as a query for SQL 2005 report builder and it started giving me all kinds of errors.

I tried it again in SQL Mangament as a View and it gave me errors again. Still works as a stand alone query though.
Re: Select 3 different 'WHERE's from same table. [message #200078 is a reply to message #200060] Fri, 27 October 2006 15:29 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Does "SQL 2005 report builder" have anything to do with Microsoft SQL Server 2005? Are you aware of the fact that this is Oracle forum (not MS SQL Server one)?

I don't know anything about MS SQL Server, but - perhaps it is DUAL table that bothers it. The rest of the query seems to be universal (i.e. should work on most databases). Could you check it?
Previous Topic: updating a table
Next Topic: Important!
Goto Forum:
  


Current Time: Sun Dec 11 02:23:30 CST 2016

Total time taken to generate the page: 0.19082 seconds