I have a table with following values
City STATE COLOR REGION
city1 Texas White SW
city2 Texas Gray SW
city3 Texas Gray SW
city4 Nevada Gray PAC
c5 Texas Yellow SW
c6 Florida Yellow SE
c7 Missouri Orange W
c8 Texas Orange SW
I am trying to create a report which looks like:
STATE White Gray Yellow Orange
Texas 1 2 0 1
Nevada 0 1 0 0
Florida 0 0 1 0
Missouri 0 0 0 1
I need to do this in oracle pl/sql
I tried to write this query
select state,
SUM( decode(color,'O range',count(st ate),0) ) as orange,
SUM( decode(color,'Y ellow',count(st ate),0) ) as yellow,
SUM( decode(color,'G ray',count(stat e),0) ) as gray
SUM( decode(color,'W hite',count(sta te),0) ) as white
from my_table
group by color
This query gives me an error "ORA-00937: not a single-group group
function"
Please help !
any ideas???
0  7732  This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: longhorntx |
last post by:
I have a table with following values
City STATE COLOR REGION
city1 Texas White SW
city2 Texas Gray SW
city3 Texas Gray SW
city4 Nevada Gray PAC
c5 Texas Yellow SW
c6 Florida Yellow SE
c7 Missouri Orange W
|
by: Brad Parks |
last post by:
The following are a few rows in my table:
StatedValue VehType Deductible Rate
10000 Truck 1000 9.16
11000 Truck 1000 9.02
12000 Truck 1000 8.82
13000 Truck 1000 8.64
14000 Truck 1000 8.51
15000 Truck 1000 8.33
|
by: Charles Law |
last post by:
I have posted this on a couple of Access and SQL Server newsgroups, but with
no answer yet. There seems to be very little activity on the ones I have
looked at, so I thought I would post here as well, since I have no doubt
that many people here also use Access/SQL Server. Anyway, enough of my
rambling and on with the problem/question.
I have two queries that I use in Access:
<qrySelect>
SELECT table2.column1, table2.column2,...
|
by: John |
last post by:
Does anyone have any suggestions on a way to automate sending an
Access pivot (query) using VBA Access and Outlook?
|
by: Gert v O |
last post by:
Can someone help me parsing this ms-access PIVOT sql-statement to a
ms-sql-server sql-statement?
Many thanks in advance
TRANSFORM Count(KlantenStops.id) AS AantalVanid
SELECT KlantenStops.Uitvoerder, KlantenStops.Klant
FROM KlantenStops
GROUP BY KlantenStops.Uitvoerder, KlantenStops.Klant
PIVOT DatePart("m",leverdatum,1,0) In
("1","2","3","4","5","6","7","8","9","10","11","12");
| | |
by: syl |
last post by:
Hi
I have a huge loto data and i am trying to come up with the total count of all total combination...the manual process to tiring in excel. e.g the possible combinations range from 1 up to 49. so i have to find the combination of numbers e.g 1 2 3 , 1 2 4, 1 2 5, 1 2 6 up to 1 2 49 then move up to 2 3 4 up to 2 3 49 until i will do the same count for 3, 4, 5 6 up to 49.
Here is a sample
...
|
by: barretldw |
last post by:
Hi, i'm trying to convert the following query:
TRANSFORM Sum(tblAssortiment.Aantal) AS SumOfAantal
SELECT tblAssortiment.Assortiment
FROM tblAssortiment
WHERE (((tblAssortiment.Artikel)=?))
GROUP BY tblAssortiment.Assortiment
PIVOT tblAssortiment.Maat;
Any ideas how to do this?
|
by: smckibbe01 |
last post by:
I have Pivot Table query in MS Access that I would like to run in SQL Server 2005. Can anyone help me to convert the syntax over to SQL Server. The following is the query in MS Access:
TRANSFORM SUM(ps.psScheduled)
SELECT pi.piMaxTurns,
pi.piAMP,
pi.piTons,
'',
'',
ps.psPipe
|
by: reachravi70 |
last post by:
Hello Users,
I am new to PERL. There is a requirement to write a PERL script to query the Oracle data based on the user input date parameter and write the output to Excel.
I have the following query below
SELECT NODE, port, floor(mtr/86400) || 'd'||to_char(to_date(mode(mtr,86400,'sssss'),' hh24"h" mi"m" ss"s" ') DOWNTIME FROM INTERFACE_DOWNTIME WHERE begindate >= to_date(¶m1,'DD/MM/YY HH:MI') + (8/24) and enddate <=...
|
by: NareshN |
last post by:
Hi,
I am getting error because of this line in stored procedure.How to enclose string value.
Count(Case When Totalhours<='25:0' Then EmpNo end) as Less.
25:0 is varchar datatype so i am enclosing in quotations.
ALTER PROCEDURE .(@date1 DATETIME, @date2 DATETIME, @date3 DATETIME, @date4 DATETIME)
AS
DECLARE @query VARCHAR(MAX)
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
