Hi ALL........
I count the Space References named as level4itemkey to the properties Schools. those properties are having the Assettypekey.
My problem is......
SELECT School.UPRN, School.SchoolName, School.DfeeNumber, Count(Level4Item.Level4ItemKey) AS CountOfLevel4ItemKey, Site.AssetTypeKey
FROM School, Site, Level4Item, Block
WHERE (School.SchoolKey = Block.SchoolKey)
AND (Block.BlockKey = Level4Item.BlockKey)
AND (Site.SiteKey = School.SiteKey)
GROUP BY School.UPRN, School.SchoolName, School.DfeeNumber, Block.BlockNumber, School.Active, Level4Item.Active, Site.AssetTypeKey
HAVING (((Block.BlockNumber)<>"EXT") AND ((School.Active)=1) AND ((Level4Item.Active)=1))
ORDER BY Count(Level4Item.Level4ItemKey) DESC;
For the above query i got the result like below
Level4Itemkey AssettypeKey
455 135
390 135
64 135
78 136
983 136
678 140
34 140
Now i want to sum these Level4Itemkeys According to the AssetTypekey.
for eg 140 sum is 712 like that,
How can i count theose. iam trying to give the one more count to that count, but its giving the syntax error.
Please help me.
iam waiting for your reply
4 1478
Hi ALL........
I count the Space References named as level4itemkey to the properties Schools. those properties are having the Assettypekey.
My problem is......
SELECT School.UPRN, School.SchoolName, School.DfeeNumber, Count(Level4Item.Level4ItemKey) AS CountOfLevel4ItemKey, Site.AssetTypeKey
FROM School, Site, Level4Item, Block
WHERE (School.SchoolKey = Block.SchoolKey)
AND (Block.BlockKey = Level4Item.BlockKey)
AND (Site.SiteKey = School.SiteKey)
GROUP BY School.UPRN, School.SchoolName, School.DfeeNumber, Block.BlockNumber, School.Active, Level4Item.Active, Site.AssetTypeKey
HAVING (((Block.BlockNumber)<>"EXT") AND ((School.Active)=1) AND ((Level4Item.Active)=1))
ORDER BY Count(Level4Item.Level4ItemKey) DESC;
For the above query i got the result like below
Level4Itemkey AssettypeKey
455 135
390 135
64 135
78 136
983 136
678 140
34 140
Now i want to sum these Level4Itemkeys According to the AssetTypekey.
for eg 140 sum is 712 like that,
How can i count theose. iam trying to give the one more count to that count, but its giving the syntax error.
Please help me.
iam waiting for your reply
Try Below Query: -
SELECT
-
School.UPRN,
-
School.SchoolName,
-
School.DfeeNumber,
-
Count(Level4Item.Level4ItemKey) AS CountOfLevel4ItemKey,
-
Site.AssetTypeKey,
-
SUM(Level4Item.Level4ItemKey) OVER(PARTITION BY Site.AssetTypeKey)
-
FROM
-
School, Site, Level4Item, Block
-
WHERE (School.SchoolKey = Block.SchoolKey)
-
AND (Block.BlockKey = Level4Item.BlockKey)
-
AND (Site.SiteKey = School.SiteKey)
-
GROUP BY School.UPRN, School.SchoolName, School.DfeeNumber, Block.BlockNumber, School.Active, Level4Item.Active, Site.AssetTypeKey
-
HAVING (((Block.BlockNumber)<>"EXT") AND ((School.Active)=1) AND ((Level4Item.Active)=1))
-
ORDER BY Count(Level4Item.Level4ItemKey) DESC
-
its giving a missing operator expression in OVER
its giving a missing operator expression in OVER
Please post the ORA error code
i got it. Thanks for your help.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ling Lee |
last post by:
Hi all.
I'm trying to write a program that:
1) Ask me what file I want to count number of lines in, and then counts the
lines and writes the answear out.
2) I made the first part like this:
...
|
by: Geetha |
last post by:
I searched in the Oracle documents what count (1) meant and I could
not find an answer. Can some one explain what Oracle does internally
when use count (1) VS count (*). Thank you very much in...
|
by: JD |
last post by:
Hi guys
I'm trying to write a program that counts the occurrences of HTML tags
in a text file. This is what I have so far:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
...
|
by: Eric Johannsen |
last post by:
I have a simple object that inherits from CollectionBase and overrides the
Count property:
namespace MyTest
{
public class CollTest : System.Collections.CollectionBase
{
public override int...
|
by: Gary Wessle |
last post by:
Hi
I have a vector<charwhich looks like this (a d d d a d s g e d d d d
d k)
I need to get the biggest count of consecutive 'd'. 5 in this example
I am toying with this method but not sure if...
|
by: heckstein |
last post by:
I am working in Access 2002 and trying to create a report from our company's learming management system. I am not a DBA and most of my SQL knowledge has been self taught through trial and error. I...
|
by: MP |
last post by:
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source="...
|
by: Auddog |
last post by:
I have the following query that works in mysql:
select id, order_no, price, count(item_no), sum(price) from production
WHERE item_no = '27714'
group by item_no;
When I setup my query in php,...
|
by: Chris |
last post by:
I am trying to increase/decrease the value of $_SESSION by 1
after clicking on a link e.g index.php?gotoWk=nxtWk and index.php?
gotoWk=lstWk. I'm sure you will get the drift if you look at the code...
|
by: jlt206 |
last post by:
This code <?php include("counter.php")?> on the webpage produces the count number. (function code below)
I want to place the current number into a variable $MemberNo or into a FormField to be sent...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |