|
SELECT Sum(Amount) as "Total Expenditures", Month(eDate) as "Month"
FROM Expenditure
GROUP BY Month(eDate)
ORDER BY Month(eDate)
Above is my current SQL statement. I would like to change it so that it would generate a table that shows zeros for months that have zero "Total Expenditures". Currently it only shows amounts for months that have
total > 0. I think I could do it once I get it into my datatable but the query would be nicer.
Thanks
"It has become appallingly obvious that our technology has exceeded our humanity."
- Albert Einstein (1879-1955)
"I think there is a world market for maybe five computers."
- Thomas Watson (1874-1956), Chairman of IBM, 1943
"640K ought to be enough for anybody."
- Bill Gates (1955-), in 1981
"Half this game is ninety percent mental."
- Yogi Berra
|
|
|
|
|
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
1 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=1
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
2 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=2
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
3 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=3
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
4 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=4
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
5 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=5
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
6 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=6
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
7 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=7
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
8 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=8
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
9 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=9
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
10 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=10
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
11 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=11
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
12 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=12
It's not the nicest way in the world, I admit. I'll let you know if I come up with a better solution. HTH.
Jeff Varszegi
P.S. You should join up-- membership's free, you know. That way you can get an email when somebody answers one of your posts. JKV
|
|
|
|
|
Yeah--- I see where that should work but when I run it in QTADO it tells me that there are :
Wrong number of arguments used with function in query expression 'ISNULL(SUM(Amount), 0)'.
From my understanding it works like this ISNULL(expression, value if null) so it should work. Maybe it is something with MS JET or something.
"It has become appallingly obvious that our technology has exceeded our humanity."
- Albert Einstein (1879-1955)
"I think there is a world market for maybe five computers."
- Thomas Watson (1874-1956), Chairman of IBM, 1943
"640K ought to be enough for anybody."
- Bill Gates (1955-), in 1981
"Half this game is ninety percent mental."
- Yogi Berra
|
|
|
|
|
It must be the MS JET Engine. It only wants one arguement for the ISNULL function. Back to the drawing board.
"It has become appallingly obvious that our technology has exceeded our humanity."
- Albert Einstein (1879-1955)
"I think there is a world market for maybe five computers."
- Thomas Watson (1874-1956), Chairman of IBM, 1943
"640K ought to be enough for anybody."
- Bill Gates (1955-), in 1981
"Half this game is ninety percent mental."
- Yogi Berra
|
|
|
|
|
I think jet has a different ISNULL. The syntax is ISNULL(value).
Try using IIF to check the condition and set it to 0 if it is null.
e.g. IIF(ISNULL(value),0, value).
|
|
|
|
|
Good one. I shouldn't have blindly assumed that he was using SQL Server, I guess.
Regards,
Jeff Varszegi
|
|
|
|
|
Yes this works:
SELECT IIF(ISNULL(Sum(Amount)),'0', Sum(Amount)) as 'Total Expenditures', '1' as 'Month'
FROM Expenditure
WHERE MONTH(eDate)=1
I suppose I will write it as Jeff suggested using the above.
Thanks for the help guys! THE CODE PROJECT IS AWESOME!
|
|
|
|
|
I would recommend just setting up a months table with 1 columns (month number). Then join that to your query (something like ... month(a.edate) = b.month_nm ...)
|
|
|
|
|
Hi!
Could you show me the difference between ADO and DAO?
Thanks!
|
|
|
|
|
DAO = Data Access Objects
ADO = ActiveX Data Objects
in brief .. use ADO .. don't use DAO
ADO is more general .. you can access many data sorces : XML,Active Directory,File System + SQL server , oracle , MySQL etc.. using OLEDB provides or ODBC..
DAO is more dedicated for access..ADO can work perfectly with Access.
|
|
|
|
|
|
Everytime I spoke with other Sun/Java flavoured developer they sneered at M$ SQL Server as if it's not a real database server. Is Oracle really so much faster than SQL? I also heard Oracle has a more comprehensive set of reporting tools. Personally, I have no experience with Oracle...
|
|
|
|
|
The current TPC-C[^] raw performance benchmarks suggest that Oracle on HP/UX on outlandish hardware (64-way Itanium2 1.5GHz, 512Gb of memory) does currently outperform SQL Server 2000 (64-bit) on the same outlandish hardware (look at the second and third rows, the first had 1024Gb of RAM).
However, most of us are lucky to be using 512 megabytes rather than 512 gigabytes.
Frankly, SQL Server usually performs well enough on almost any hardware. Oracle really requires massive amounts of memory - 9i couldn't create a database on our test system with 192Mb (taking 6 hours and then the client crashed), while SQL Server 2000 creates a new database in about 5 seconds on the same hardware.
Basically, you need to decide what you're going to use your system for (transaction processing versus ad-hoc queries), decide what performance level you actually need (i.e. how many transactions per second you're planning to handle). The performance of your system will also depend very much on how you write your queries and implement your indexes.
For most general purpose applications, SQL Server is fine.
|
|
|
|
|
Mike Dimmick wrote:
Frankly, SQL Server usually performs well enough on almost any hardware. Oracle really requires massive amounts of memory - 9i couldn't create a database on our test system with 192Mb (taking 6 hours and then the client crashed), while SQL Server 2000 creates a new database in about 5 seconds on the same hardware.
I looked at the URL you specified... SQL Server isn't doing so bad at all.. But I remember I read some performance reports from Oracle site. According to their report, SQL performance is just a small fraction of Oracle.. I didn't read into the details however.
|
|
|
|
|
Mike Dimmick wrote:
Frankly, SQL Server usually performs well enough on almost any hardware. Oracle really requires massive amounts of memory - 9i couldn't create a database on our test system with 192Mb (taking 6 hours and then the client crashed), while SQL Server 2000 creates a new database in about 5 seconds on the same hardware.
Not to mention, SQL Server is organized in such a way as to make it easy for "non-sql" people to use. With the different flavors (MSDE, Standard, Developer, and Enterprise), you can really get just what you need without the tons of overhead that Oracle requires.
Also, with Oracle, from around 7.0.4 and above, you basically need at least a week of classes just to figure out the whole Scott/Tiger thing and how to get the DB up and running. That does not include performance tuning, backups or how and why the data dictionary works.
Mike Dimmick wrote:
Basically, you need to decide what you're going to use your system for (transaction processing versus ad-hoc queries), decide what performance level you actually need (i.e. how many transactions per second you're planning to handle). The performance of your system will also depend very much on how you write your queries and implement your indexes.
We did a lot of performance testing on both Oracle 9i and Sql Server 2K. We found that when it came to data warehousing (large queries, mostly static indexes and a lot of tables!), Oracle was best. When it came to more transactional queries, Sql Server was top notch. Plus, given that .NET is tailored to Sql Server (go figure!), there is an added performance boost... 
Ok, enough of my rambling... 
Bill P.
Oakland, CA
-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCM/MU/B dpu s--:-- a32 C++++$ ULH+++ P+++ L++ E+ W+++$ N++ o K? w++++$ O-- M V-- PS+ PE+ Y++ PGP++ t++@ 5++@ X++ R+@ tv b++ DI++ D+++>++++ G++ e++ h---- r+++ y++++
-----END GEEK CODE BLOCK-----
|
|
|
|
|
As others have said, it all depends on your available hardware and the type of DB you want to create. I have found SQL Server to be much easier to use (in general), easier to manage for most day-to-day taks, has better management tools and performs well on updates/deletes and good-enough on selects. Oracle has poorer management tools, but has many more configuration options readily accessible and performs better for querying, but unless configured to the Nth degree, has poor performance on inserts/updates/deletes.
If you know how to configure Oracle, I think you can squeeze better performance out of it on an apples-for-apples hardware comparison. Also, Oracle IMHO is more scalable to multiple servers, especially in geographically seperated situations.
Ultimately, I choose SQL Server because I just don't know how to configure all the options and SQL Server sets most things automatically and does a good enough job. About all you need to know specific to SQL server (beyond basic DB design) is how to use file groups. Also, the management tools for SQL Server are much better than the comporable Oracle tools.
|
|
|
|
|
Hi everyone,
I have not been in touch with SQL for a long time. I had to start using it again since a few weeks and it has been quite a struggle.
I have a question and I would be really grateful if someone can help me with it. Consider the following table structure:
ID TESTNUMBER STATE VALUE
1 100 1 0
2 100 1 1
3 100 2 1
4 100 2 0
5 101 1 1
6 101 1 0
7 101 2 1
Now, what I want to do is get the DISTINCZ count of TESTNUMBER, say where (State = 1, Value = 0) and (State = 2, Value = 1). So basically this should give me a count of 2 because TestNumber 100 has an entry for (State = 1, Value = 0) and (State = 2, Value = 1). Also, entry 101 has (State = 1, Value = 0) and (State = 2, Value = 1) entries. However, I am unable to write this using a query. Please help!
Thanks,
Sincerely,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
Try this:
SELECT COUNT(*), state, value
FROM table
GROUP BY state, value
The statement above will group your result based on state and value, e.g.
Count State Value
2 1 0
2 1 1
2 2 1
1 2 0
If you need to add a condition, add HAVING state = 1 AND value = 0 (for example).
|
|
|
|
|
Hi,
I think I did not explain my question well. Let me explain the scenario. I have a code that has to generate all possible combinations of chosen States and Values.. Say for our example the combinations are (State = 1, 0) (State = 2, 1).
Now, I have to go in the database and find the TestNumber which has an entry for (State = 1, 0) AND (State = 2, 0) (which would only be TestNumber 100. If I had used an OR in my Select query it would return both 100 and 101. Also, if I use AND for the same field it would return me nothing)... So all combinations exist for this state. What SQL construct exists for performing such queries?
Thanks,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
You can join your table to itself to do this. (I think this is what you are asking for...
select t1.testnum, count(t1.testnum)
from test t1
join test t2 on (t1.testnum = t2.testnum)
where t1.state = 2 and t1.value = 1
and t2.state = 2 and t2.value = 0
group by t1.testnum
this selects the testnum and a count from the table, joins itself based on the testnum and the where clause specifies where your first criteria matches on the "first" table and the second criteria matches on the "second" (your self join) table. Group it by testnum to see the count for each testnum that matches. Here are the results of that query...
Testnum count
100 1
If you change the t2.state = 2 to t2.state = 1, you get back
100 1
101 1
If this isn't what you are after, enlighten me some more on what you want.
|
|
|
|
|
Hi,
It had takem me a while but I figured that out. Thanks a lot for replying. I have a more complex one for you. I am really no good with SQL and unfortunately I have to work with it on this one 
Consider the following table again:
ID TESTNUMBER STATE VALUE
1 100 1 0
2 100 1 1
3 100 2 1
4 100 2 0
5 101 1 1
6 101 1 0
7 101 2 1
8 101 2 0
9 103 1 1
10 103 3 1
Now, I have to get all TESTNUMBER which have different sets of State and Values. So, this table will return 2 sets...
Set 1:
1 0
1 1
2 0
2 1
(Since, 100 and 101 share a common set)
Set 2:
1 1
3 1
(102 does have an identical set as 100 and 101. Common items are allowed but the sets must be identical (same elements))
Any suggestions on how to achieve this?
Thanks a lot,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
Honestly, something that convoluted, I would just query the table and figure out the sets in my app side code. You're more than likely going to have to loop through the records, and I'd rather do that in code than with SQL Cursors.
|
|
|
|
|
Yeah, that is what I ended up doing. I am not a big fan of SQL 
Thanks,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
ListObjectPermissions
what does the above mention statement do??
can i use this to get the access permissions of a user?
ranjani
|
|
|
|
|
ListUserPermissions
what does this method do..
does this tell me the user access perm for a particular database?
ranjani
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
