Hello
What I need to do is be able to group the results of my select statements in different columns. And end having the result work like this.
campaign Col1 Col2
<<Data>> <<Select counT(*) where field= value>> <<Select counT(*) where field= value>>No you don't. You just think you do. What you really want to do is create a CROSSTAB query. Look it up in Books Online.sql
Showing posts with label clause. Show all posts
Showing posts with label clause. Show all posts
Friday, March 30, 2012
Grouping question
If I want to get a count of each type of item in a table, I can use a simply GROUP BY and include a Count() of what I want in the SELECT clause.
But how do I get subcounts of that data? Like I want to group by a first value, then show columns for all the possible values in a second column. For instance, let's say I have two columns FirstName and LastName. What would a t-sql statement look like to yield the following output? It's like using the Column gropuing in an Excel pivot table.
Is this possible?
| Totals | ||
|---|---|---|
| LastName | John | Jane |
| Doe | 13 | 8 |
| Schmoe | 6 | 4 |
Thanks!
Dave
This is not possible to do in SQL without going through lot of hoops. You should do report generation on the client-side. Given few restrictions, you can generate a pivotted result like above.Monday, March 12, 2012
GROUP BY/ HAVING CLAUSE problem
I'm trying to set up my adhoc query to return just one single record, which is aliased as 'foreign' in my sql statement (which is just the total amount of foreign overseas orders for just one day. All Sale_Type_Ids over 2 [integer datatype] are foreign orders):
SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) AS foreign
FROM Orders INNER JOIN
Processing ON Orders.ID = Processing.Order_ID
WHERE (Processing.Orderdate = '20050915') AND (Processing.status = 1)
GROUP BY CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END
HAVING (SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) >= 0)
..but my resultset is returning two records. If I remove the HAVING clause, it will return three records, with one being blank.
?
.netsports
http://www.akadia.com/services/dealing_with_null_values.html|||i am using four table(ForumMain,ForumThreads,ReplyToThread,Authentication) in my forum.I have 4 asp.net pages in this forum. On the very first page, I am showing the Main category of forums.i.e all forums,last thread posted,total threads so far and the total number of replies to each thead and of course the name of the user who generated or added last thread.
To do this, i am using count function to count the total replies to each thread,RepliesToThread table is doing that(not counting total threads yet),Forum Category field from the ForumMain table,ThreadName from the ForumThreads table and the username from the Authentication table.
I am using Group By clause as well but every time a new thread is added from AddThread.aspx page, the name of the main category which the new thread is added into, is repeated on the main page.
i.e. if I add a new thread in main category DATABASE, and this main category has already one thread, the main page show me like
DATABASE already existing category.
date:25/09/2005
DATABASE new category
date:26/09/2005
rather than it should show me
DATABASE new category
date:26/09/2005
What should I do to avoid this repetition?
Thanks in advance.|||Try the link below and see if GROUP BY with CUBE or ROLLUP operator will help with you problem and some restrictions apply. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp|||Caddre, this linke you providehttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp is not providing help to solve my problem.
I am looking forward to more helpful replies from you or anybody else.|||Hi,
You're summingSale_Type_Id values from Orders table. I thinks this is not what you want to get. You can Count theSale_Type_Id values to have the number of orders.
SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) AS [foreign]
And if your records in Orders table have Sale_Type_Id values greaterthan 2, for each distinct value of Sale_Type_Id you'll get a differentrow.
Because you group your records due to Sale_Type_Id's. Note that if itis 2 or less. You group them as nulls. And remove only the null groupby using the Having clause.
So you still have groups having Sale_Type_Id's greater than 2
I hope it is helpfull
Eralper
http://www.eralper.com
Group by, creating a headache
I am using SQL server 2000.
While using query analyzer I am facing problem.
If a query has group by clause and if that query is not fetching any record (i.e. query is returning nothing), then in this situation, I want zero to be displayed where datatype of field is integer and "-" if datatype of field is varchar.
Please give me solution as soon as possible, a kind request.
Facing problem for the below mentioned query:-
select IsD.ItemCode,
case
when sum(IsD.IssuedQty) is null then 0
else sum(IsD.IssuedQty)
end as IssuedToday
from Inv_IssueMaster IsM, Inv_IssueDetail IsD
where IsM.IssueNo=IsD.IssueNo
group by IsD.ItemCode
In the above mentioned query, datatype of IssuedQty is int and for ItemCode it's varchar.use the CASE operator for these columns where you need conditional values.
Hope this helps.|||Please give me solution as soon as possible, a kind request.please show your query|||I am using SQL server 2000.
While using query analyzer, I am facing problem.
If a query has group by clause and if that query is not fetching any record (i.e. query is returning nothing), then in this situation, I want zero to be displayed where datatype of field is integer and "-" if datatype of field is varchar.
Please give me solution as soon as possible.
Facing problem for the below mentioned query:-
select IsD.ItemCode,
case
when sum(IsD.IssuedQty) is null then 0
else sum(IsD.IssuedQty)
end as IssuedToday
from Inv_IssueMaster IsM, Inv_IssueDetail IsD
where IsM.IssueNo=IsD.IssueNo
group by IsD.ItemCode
In the above mentioned query, datatype of IssuedQty is int and for ItemCode it's varchar.|||I want zero to be displayed where datatype of field is integer and "-" if datatype of field is varchar.
I'm having trouble understanding your objective.
You have 2 columns from 2 tables, the 2nd is 0 to many relationship: first Varchar, 2nd Int
If the input is:
A 10, 20, 25
B null
C 15
You want results of
A 55
- 0
C 15
Is that correct? Why are you saying you want to check datatype
Note: Are you aware of the "ISNULL" function? It's a poor-man's DECODE (Oracle's powerhouse fuction).
I'm wondering if your question about datatype was just a confusion and this is all you want (I took the liberty of adding an outer join in case you have no detail records, you may still want to show a zero).
SELECT IsD.ItemCode,
sum(isnull(IsD.IssuedQty,0)) as IssuedToday
FROM Inv_IssueMaster IsM
LEFT OUTER JOIN Inv_IssueDetail IsD
ON IsM.IssueNo=IsD.IssueNo
GROUP BY IsD.ItemCode
This would return:
A 55
B 0
C 15
If you really want the "-" to be output in the first column, then this would work:
SELECT
CASE WHEN IssuedToday = 0 then '-'
ELSE SubQry.ItemCode end as ItemCode,
IssuedToday
FROM
(
SELECT IsD.ItemCode,
sum(isnull(IsD.IssuedQty,0)) as IssuedToday
FROM Inv_IssueMaster IsM
LEFT OUTER JOIN Inv_IssueDetail IsD
ON IsM.IssueNo=IsD.IssueNo
GROUP BY IsD.ItemCode
) SubQry
This will return:
A 55
- 0
C 15|||select IsD.ItemCode
, coalesce(sum(IsD.IssuedQty),0) as IssuedToday
from Inv_IssueMaster IsM
inner
join Inv_IssueDetail IsD
on IsD.IssueNo = IsM.IssueNo
group
by IsD.ItemCodethe only way that this query "is not fetching any record (i.e. query is returning nothing)" is when there are no rows in the Inv_IssueMaster table, which doesn't seem likely|||select IsD.ItemCode
, coalesce(sum(IsD.IssuedQty),0) as IssuedToday
from Inv_IssueMaster IsM
inner
join Inv_IssueDetail IsD
on IsD.IssueNo = IsM.IssueNo
group
by IsD.ItemCodethe only way that this query "is not fetching any record (i.e. query is returning nothing)" is when there are no rows in the Inv_IssueMaster table, which doesn't seem likely
Interesting use of COALESCE instead of ISNULL. Somehow I sense that I'm unaware of some subtlity. Partell.:shocked: (eagerly awaiting lesson)
As for no records in the Master - that would normally be true in a typical table structure, but it is possible to set up such a relationship. It would be a strange and badly normalized design, but SQL would allow it.
For example; let's say a school has the guy's SSN for student's that earn money from them and instead of linking the payment file into the student-ID, they have to link it to the SSN because the payment file doesn't have a "Student ID" column. They should create an intermediate XREF table, but they could also get lazy and just add the SSN to the STUDENT table. So this report would show "-" in the SSN column when there isn't one.
Somehow; given the OP's inconclusive wording of the requirement I'm wondering if that's really what s/he ment though. I think his problem is solved and s/he may or may not return to clarify.|||Interesting use of COALESCE instead of ISNULL. Somehow I sense that I'm unaware of some subtlity. Partell.:shocked: (eagerly awaiting lesson)interesting? how about standard sql ;)
coalesce is standard, isnull isn't
perhaps too subtle...|||interesting? how about standard sql ;)
coalesce is standard, isnull isn't
perhaps too subtle...
Thanks for the response.
ISNULL is just a trimmer version (ie: smaller Object) of COALESCE so is likely a little faster. However it's propietary so not portable.
So; sounds like a combination of habit, your need for portability, and maybe a general distain for propietary deviations from ANSI. Not some performance or reliability (within SQL Server) trick.
I'll have to rethink some of those things if I ever write something that has to be portable. For now, in a SQL Server only shop, I'll opt for using the trimmer version, considering it gets used so many times.
I see your point and it's a good one. I'm sure I'll continue using ISNULL, and when using Oracle I'll use DECODE, but I'll better appreciate why a Sr. SQL Consultant might do otherwise.
(Quite honestly; I had forgotten that ISNULL wasn't standard. The danger of being a single shop guy - need to get out more. Ergo my sudden appearence on this forum. :) )|||COALESCE blows the socks off ISNULL when there are more than two terms in the list ;)
likely faster? you cannot say that without extensive benchmarking, can you
and what's a "smaller Object" -- is that some kind of object-oriented thingy? in which context would you need to measure this object size?
i wasn't aware that the compiled execution plan would actually be bigger for one function in a query as compared to another|||COALESCE blows the socks off ISNULL when there are more than two terms in the list ;)
likely faster? you cannot say that without extensive benchmarking, can you
and what's a "smaller Object" -- is that some kind of object-oriented thingy? in which context would you need to measure this object size?
i wasn't aware that the compiled execution plan would actually be bigger for one function in a query as compared to another
Yes, object oriented thingy. General overhead thing. Use the minimum.
Extensive benchmarking - I'd rather just take an educated guess and say "probably".
Why would MS make it if not to optimize things a little? If they just did it for readability, and they actually made it slower, well ... that's just very unlikely. If I said "probably blow's it's socks off performance wise", now that would be an irresponsible statement. I'd stand by my original statmenet and say that common sense (and many years of programming experience) suffice for a "probably".
"more than two terms in the list", well, that would be a different capability, more in line what that extra code is ment to handle. I think "blow socks off" is a misnomer. It's more binary than that, like SELECT vs. SET, since ISNULL does not accept multiple terms.
Anyway; I didn't mean to start a shouting match. I acknowledge that you're probably far senior to myself in such matters and was ernestly looking for an insight. Your point is well taken and I believe it's a valid one.
Cheers :beer:|||Why would MS make it if not to optimize things a little? oh! oh! i know this one! to be compatible with sql standards, maybe?
but thanks for the followup, and rest assured, i wasn't shouting
:cool:|||oh! oh! i know this one! to be compatible with sql standards, maybe?
but thanks for the followup, and rest assured, i wasn't shouting
:cool:
See, now I'm curious about the overhead.
Indeed, why would MS deviate from ANSI standard on this?
My general project management experience tells me someone made a hit-list of "how to optimize, how to simplify". However; was it part of a marketing conspiracy to prevent SQL Server shops from migrating out? haha.
If the former, then the gain would have to be substantial to justify it - or at least very easy to implement with a modest gain.
Guess someone with an IN to the MS development team, or a very in depth book on SQL Server "improvements" would have to answer that one. Or like you say, develop a benchmark. Honestly; I'm not that curious.|||Another deviation from ANSI SQL is the GROUP BY ALL (see BOL). Works OK if all you need to deal with is SQL Server.|||"more than two terms in the list", well, that would be a different capability, more in line what that extra code is ment to handle. I think "blow socks off" is a misnomer.i should have been a bit more explicit
COALESCE is easier to write, to understand, and to maintain ( = "blows the socks off") than a series of nested ISNULLs, when what you need to do is select the first non-null value in a series of values|||Which also means, thaty you would have to use ISNULL many time to mimic the COALESCE function, so performance point goes out the window
Also, I stay as ANSI as possible to because I work on many different database platforms...it's tough enough changing gears as it is
Man I hate DB2 OS/390 a lot these days|||ooohhhh sigh.....
always stick to the ANSI-92 as close as possible. My hands are not clean in this matter. I have wrtten some proprietary junk.
screw having to port something to oracle or mysql or whatever. ask people what they think about moving to MS SQL 2005 that have a bunch of *= and =* in their code instead of nice and proper OUTER JOIN statements.|||I absolutely love COALESCE!!! My shop here (and pretty much all new SQL coders I have seen in the past 4 years) take to ISNULL as if it were a heated jockstrap on a backpacking trip to Eagle Lake (Sierras) in early March!!!
I just love COALESCE. If I wasn't married, I would marry COALESCE. I have cleaned up much stacked, squeezed, haywired, and Elmer's Glued ISNULL nests with a single COALESCE that, in it's own sublime beauty, brings tears to the eyes of the SQL youngsters.
Put simply, I feel strongly that COALESCE is cleverer than ISNULL. I think COALESCE could whip ISNULL's butt in a towel-snapping fight, and in most recognized games of chance available in the world today.
I had no idea that ISNULL is not ANSII standard though, so I am thrilled to have read this thread. It's really about time that COALESCE got the press it deserves.
If I have not mentioned it, it is, in fact, probably my favorite verb in MS SQL.|||Guess someone with an IN to the MS development team, or a very in depth book on SQL Server "improvements" would have to answer that one.
Friend of friend was a dev on the sql server team <wink>. Didn't code up the ISNULL feature though, so can't speak with authority on its origins :)
Indeed, why would MS deviate from ANSI standard on this?
I am pretty sure that ISNULL did not come about because of some MS conspiracy to try to get people to move away from existing ANSI standards.
More likely is that ISNULL was the pet feature of some long lost PM. Fair bet that he/she wasn't even aware of the existence of COALESCE when they came up with it. :)|||I don't know of any benchmarks showing one is faster than the other. The difference would be less than negligible anyway.
Why did MS create ISNULL()? I don't know that they did. It was probably a part of SYBASE before Microsoft got a hold of it. Also suspicious that ISNULL() in MSSQL operates completely differently than ISNULL() in MSAccess, making it even more unlikely that it was Microsoft's idea.
If you have a choice between two methods and one is ANSI while the other is not, use the ANSI standard.|||Love finding such passion. Tall dude, careful what you wish for (marriage wise), lol.
Oracle has "nvl", SQL Server "isnull".
I'm opting for using stuff as appropriate. COALESCE just seems more like a parser of a string of potentially null values than a simple replacment converter. Like a conditional branch statement that has a conversion method as a side benifit. Just my impression.
Using it with a single arguement just seemed odd to me, but then, I've only used Microsoft and Oracle platforms.
Nesting ISNULL does seem messy. Wattya wanna bet (ok, nothing over 50 cents) that COALESCE actually uses ISNULL internally. Would certianly make sense, programming wise. Why repeat all that coding?
Anyway, hat's off to the "ANSI-92 Only" camp. I sure can't keep up with it.
Shame when we get spoiled with some Propietary feature. Main one I sorely miss is the PL SQL support for a "Cursor Loop". All the cursor's fields get pseudo created with a scope only within the loop, no extra definition required, and more importantly all the definitions and naming inhereted at compile time and therefore self-maintaining if someone comes along and changes a column definition. Yeah; what a time and code saver! I'll deviate any day for that one. I'm just an ignorant "only solve what's in front of me" guy so I have no idea if that's in the ANSI standard or not - or if SQL Server 2005 has something similar.|||SQL Server supports cursors?
I'm not the best one to answer that one...as the Great Sage has been saying for incalcuable millennia, "Nature Abhors A Cursor" (or something to that effect, anyway).
I have managed to use only two in the past 4 years...and both of those to demonstrate why they should NOT be used ;)
yeah, yeah, yeah...sometimes they are necessary (or it's not worth it to do it correctly for a one-off cursor solution), but I like to pretend they don't exist. Similar to the methodology I use to address the existence of the devil himself.|||COALESCE just seems more like a parser of a string of potentially null values than a simple replacment converter.
Nesting ISNULL does seem messy. Wattya wanna bet (ok, nothing over 50 cents) that COALESCE actually uses ISNULL internally. Would certianly make sense, programming wise. Why repeat all that coding?
Anyway, hat's off to the "ANSI-92 Only" camp. I sure can't keep up with it.
I wouldn't muddy ISNULL() the function with the concept of something being null. COALESCE and ISNULL are funtionally identical at the conceptual level for evaluating the first argument. COALESCE is not a parser by any stretch of the imagination. COALESCE just has infinantly more flexibility with a very small perceived performance hit.
Assume you have:
ISNULL(myScalarVal, myScalarResult)
and
COALESCE(myScalarVal, myScalarResult)
logically speaking, both functions must perform the exact same scalar comparison for null against myScalarVal. The difference is COALESCE will now have to perform the same scalar comparison against myScalarResult to determine if it is also null. This could be viewed as a minor performance hit, although scalar comparisons are about as trivial of an action as I can imagine.
Now assume we have:
ISNULL(myScalarVal, ISNULL(mySecondScalar, ISNULL(myThirdScalar, myScalarResult)))
and
COALESCE(myScalarVal, mySecondScalar, myThirdScalar, myScalarResult)
Now we have to execute three seperate statements, returning the result of each nested statement to its parent statement. There is inherint overhead in having multiple levels of recursion waiting on a return value. COALESCE by comparison does this intrinsically, not much caring how many values it's been given. It simply plods along looking for the first non-null value and throws away anything that doesn't satisfy this criteria as opposed to passing the failed results back to a calling function.
So all in all, I don't think there's much of a difference between the two for testing and substituting a single null value. However there are great benefits in performance, readibility and flexibility in using COALESCE for multi-element comparisons, so why not use COALESCE out of hand all the time?|||SQL Server supports cursors?
...
I have managed to use only two in the past 4 years...and both of those to demonstrate why they should NOT be used ;)
They're sure slow hua? Here I'm arguing over trivia and start talking about using something about 10 times slower. sigh. And 10 times clunkier to program.
Usually it's a conversion program or integrity check - but in production ... there's always some kind of alternative. Sometimes however, what the heck's the difference between .1 second and 2 seconds for a once a month process.
... That said, why not use COALESCE out of hand all the time?
Well, I'm sold. Quite honestly (I admit :eek: ), the answer is "harder to type and spell".|||Well, I'm sold. Quite honestly (I admit :eek: ), the answer is "harder to type and spell".Ahhh...but there is an advantage to that as well. Noob developers looking over shoulder think it is some mystical database arcana. Abra-cadabrac-coalesce...it helps keep up the reputation.|||My Main problem is still unanswered.
Problem statement:
If a select query without group by clause, returns null under column headers, if select query doesnt fetch any record.
If a select query with group by clause, not returns null under column headers, if select query doesnt fetch any record.
I want select query should return zero for int datatype and - for varchar datatype, if select query doesnt fetch any record.|||sorry, umeshm_patil, i don't think that's possible|||Indeed, why would MS deviate from ANSI standard on this?I [b]like[b] the easy questions... When Sybase created the IsNull function, Coalesce was still about seven years from coming into existance as a proposed addition to the standard. Microsoft inherited the SQL Server product with IsNull already well established.
Actually, starting in SQL 7.0 the IsNull function is now implemented internally as a call to the same routine that serves Coalesce. There might be minute difference in the Transact-SQL parsing time (only because Coalesce has more letters than IsNull), but that difference isn't material to the function execution. If there are more than two values (implying nested calls to IsNull), then a single call to Coalesce will win hands down.
-PatP|||My Main problem is still unanswered.
Problem statement:
If a select query without group by clause, returns null under column headers, if select query doesnt fetch any record.
If a select query with group by clause, not returns null under column headers, if select query doesnt fetch any record.
I want select query should return zero for int datatype and - for varchar datatype, if select query doesnt fetch any record.What you seem to be requesting is a formatter for "non values" in your result set. These are by definition application specific, they are not truly issues for the server to manage for you. They really ought to be handled in your application code to avoid pushing application presentation issues into the realm of your SQL Server.
-PatP|||I don't know of any benchmarks showing one is faster than the other. I know of three! One I have bookmarked (good old Adam) with links to the other two:
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/30/5311.aspx
The difference would be less than negligible anyway.Bingo!
Sorry for another <ot> post umeshm_patil. FWIW - I agree with Pat. Well done Pat!|||excellent link, pootle flump|||Nice!
A 4th test, (http://jerrytech.blogspot.com/2006/05/sql-2k-performance-isnull-vs-coalesce.html) linked by someone in the comment section of pootle flump's link, showed very surprising results.
The time for a nested ISNULL was only MARGINALLY longer than a single ISNULL.
Example:
9280ms : Set @.x = IsNull(Jerry, Nixon)
9296ms : Set @.x = IsNull(Jerry, IsNull(Nixon, Value))
9500ms : Set @.x = Coalesce(Jerry, Nixon)
9563ms : Set @.x = Coalesce(Jerry, Nixon, Value)
This test showed ISNULL to be slightly faster.
Hardly an arguement for using ISNULL given it's propietary nature, but it is an arguement for not rolling one's eyes quite so loudly when some programmer nests ISNULLs.|||btw, while we are on the topic of coalesce, C# has a coalesce operator now. I just learned about it following some of the perf testing links pootle_flump provided: http://weblogs.sqlteam.com/mladenp/archive/2006/03/27/9425.aspx
with it you can do this:
string foo = bar ?? "default value";
very neat! I love the operator because when I first saw it I thought "what??"|||<cough>
drop table testproducts
create table testproducts
(col1 int,
col2 varchar(10))
insert into testproducts
select 1, 'red'
union
select 2, 'yellow'
union
select 3, 'blue'
create table testorders
(col1 int,
col2 int)
insert into testorders
select 1, 10
union
select 2, 30
union
select 1, 50
union
select 2, 1
union
select 1, 5
-- Hey! No blue orders??
select sum(o.col2), count(*), p.col2
from testproducts p left outer join
testorders o on p.col1 = o.col1
group by all p.col2
Yeah, yeah. Group by all is non-ANSI, but it gets the OP through the day.|||<cough>something wrong with your throat?
guess what results you get for this query --select sum(o.col2), count(*), p.col2
from testproducts p left outer join
testorders o on p.col1 = o.col1
group by p.col2
so your point was... ?|||Mistaken, apparently.....|||Not to stray off topic, but anyone ever run COALESCE with NULL as the final value?
Interesting...|||Not to stray off topic, but anyone ever run COALESCE with NULL as the final value? i haven't
i don't think it's ever necessary, is it ;)|||i haven't
i don't think it's ever necessary, is it ;)
Of course not. It made for an interesting academic exercise in poking around behind how coalesce works though...|||Not to stray off topicI think off topic is the new on topic.
Of course not. It made for an interesting academic exercise in poking around behind how coalesce works though...Presumably this is where you got a lot of your conclusions from ealier? Do you have any code to hand or do we have to jig up our own? :)|||My earlier conclusions were nothing more than logical conjecture as a developer. It wasn't until all those contradictory perf studies were posted that I really wanted to start monkeying with this stuff.
Anyways, try these:
SELECT CASE WHEN 1=1 THEN NULL END
SELECT CASE WHEN 1=1 THEN NULLIF(1,1) END
SELECT COALESCE (NULL, NULL)
SELECT ISNULL(NULL, NULL)
This seems to suggest that ISNULL is using a different method of comparison then COALESCE. One that could very well be faster.|||Yeah, but this works...
DECLARE @.x int
SELECT COALESCE (NULL, @.x)
So perhaps it's just syntax and/or the use of the keyword NULL?|||The query engine has no way of knowing the runtime value of @.x when compiling the query plan. That's the same reason COALESCE(NULL, NULLIF(1,1)) will function properly; NULLIF() isn't evaluated until runtime. You can do the same trick using Case.
The conclusion I'm driving at is COALESCE() is nothing more than a wrapper for CASE whereas ISNULL() is doing something entirely different. This may explain why what would appear to be the logically superior structure for evaluating multiple elements isn't always the fastest. Now the question becomes:
Which is faster?
ISNULL(myScalar1, ISNULL(myScalar2, result))
or
CASE
WHEN myScalar1 <> NULL THEN myScalar1
WHEN myScalar2 <> NULL THEN myScalar2
ELSE result
END
Edit: A bit more co-noodling with another member here brought up an interesting opinion. COALESCE, being newer than ISNULL simply implements more stringent front-end validation as it is an ANSI standard. Now I'm all messed up... :(|||here's an example that came up today on another forum
scenario: there are two tables, persons and contacts, a person can have multiple contacts, each contact is a separate row with a contact type column, and the query is supposed to return each person with at most one contact -- return the person's email contact, and if email contact doesn't exist, return the work number, and if work number doesn't exist, return the home number
select p.name
, coalesce(e.contact
,w.contact
,h.contact) as contact
from persons as p
left outer
join contact as e
on e.personid = p.id
and e.contacttype = 'E' /* email */
left outer
join contact as w
on w.personid = p.id
and w.contacttype = 'W' /* work # */
left outer
join contact as h
on h.personid = p.id
and h.contacttype = 'H' /* home # */
While using query analyzer I am facing problem.
If a query has group by clause and if that query is not fetching any record (i.e. query is returning nothing), then in this situation, I want zero to be displayed where datatype of field is integer and "-" if datatype of field is varchar.
Please give me solution as soon as possible, a kind request.
Facing problem for the below mentioned query:-
select IsD.ItemCode,
case
when sum(IsD.IssuedQty) is null then 0
else sum(IsD.IssuedQty)
end as IssuedToday
from Inv_IssueMaster IsM, Inv_IssueDetail IsD
where IsM.IssueNo=IsD.IssueNo
group by IsD.ItemCode
In the above mentioned query, datatype of IssuedQty is int and for ItemCode it's varchar.use the CASE operator for these columns where you need conditional values.
Hope this helps.|||Please give me solution as soon as possible, a kind request.please show your query|||I am using SQL server 2000.
While using query analyzer, I am facing problem.
If a query has group by clause and if that query is not fetching any record (i.e. query is returning nothing), then in this situation, I want zero to be displayed where datatype of field is integer and "-" if datatype of field is varchar.
Please give me solution as soon as possible.
Facing problem for the below mentioned query:-
select IsD.ItemCode,
case
when sum(IsD.IssuedQty) is null then 0
else sum(IsD.IssuedQty)
end as IssuedToday
from Inv_IssueMaster IsM, Inv_IssueDetail IsD
where IsM.IssueNo=IsD.IssueNo
group by IsD.ItemCode
In the above mentioned query, datatype of IssuedQty is int and for ItemCode it's varchar.|||I want zero to be displayed where datatype of field is integer and "-" if datatype of field is varchar.
I'm having trouble understanding your objective.
You have 2 columns from 2 tables, the 2nd is 0 to many relationship: first Varchar, 2nd Int
If the input is:
A 10, 20, 25
B null
C 15
You want results of
A 55
- 0
C 15
Is that correct? Why are you saying you want to check datatype
Note: Are you aware of the "ISNULL" function? It's a poor-man's DECODE (Oracle's powerhouse fuction).
I'm wondering if your question about datatype was just a confusion and this is all you want (I took the liberty of adding an outer join in case you have no detail records, you may still want to show a zero).
SELECT IsD.ItemCode,
sum(isnull(IsD.IssuedQty,0)) as IssuedToday
FROM Inv_IssueMaster IsM
LEFT OUTER JOIN Inv_IssueDetail IsD
ON IsM.IssueNo=IsD.IssueNo
GROUP BY IsD.ItemCode
This would return:
A 55
B 0
C 15
If you really want the "-" to be output in the first column, then this would work:
SELECT
CASE WHEN IssuedToday = 0 then '-'
ELSE SubQry.ItemCode end as ItemCode,
IssuedToday
FROM
(
SELECT IsD.ItemCode,
sum(isnull(IsD.IssuedQty,0)) as IssuedToday
FROM Inv_IssueMaster IsM
LEFT OUTER JOIN Inv_IssueDetail IsD
ON IsM.IssueNo=IsD.IssueNo
GROUP BY IsD.ItemCode
) SubQry
This will return:
A 55
- 0
C 15|||select IsD.ItemCode
, coalesce(sum(IsD.IssuedQty),0) as IssuedToday
from Inv_IssueMaster IsM
inner
join Inv_IssueDetail IsD
on IsD.IssueNo = IsM.IssueNo
group
by IsD.ItemCodethe only way that this query "is not fetching any record (i.e. query is returning nothing)" is when there are no rows in the Inv_IssueMaster table, which doesn't seem likely|||select IsD.ItemCode
, coalesce(sum(IsD.IssuedQty),0) as IssuedToday
from Inv_IssueMaster IsM
inner
join Inv_IssueDetail IsD
on IsD.IssueNo = IsM.IssueNo
group
by IsD.ItemCodethe only way that this query "is not fetching any record (i.e. query is returning nothing)" is when there are no rows in the Inv_IssueMaster table, which doesn't seem likely
Interesting use of COALESCE instead of ISNULL. Somehow I sense that I'm unaware of some subtlity. Partell.:shocked: (eagerly awaiting lesson)
As for no records in the Master - that would normally be true in a typical table structure, but it is possible to set up such a relationship. It would be a strange and badly normalized design, but SQL would allow it.
For example; let's say a school has the guy's SSN for student's that earn money from them and instead of linking the payment file into the student-ID, they have to link it to the SSN because the payment file doesn't have a "Student ID" column. They should create an intermediate XREF table, but they could also get lazy and just add the SSN to the STUDENT table. So this report would show "-" in the SSN column when there isn't one.
Somehow; given the OP's inconclusive wording of the requirement I'm wondering if that's really what s/he ment though. I think his problem is solved and s/he may or may not return to clarify.|||Interesting use of COALESCE instead of ISNULL. Somehow I sense that I'm unaware of some subtlity. Partell.:shocked: (eagerly awaiting lesson)interesting? how about standard sql ;)
coalesce is standard, isnull isn't
perhaps too subtle...|||interesting? how about standard sql ;)
coalesce is standard, isnull isn't
perhaps too subtle...
Thanks for the response.
ISNULL is just a trimmer version (ie: smaller Object) of COALESCE so is likely a little faster. However it's propietary so not portable.
So; sounds like a combination of habit, your need for portability, and maybe a general distain for propietary deviations from ANSI. Not some performance or reliability (within SQL Server) trick.
I'll have to rethink some of those things if I ever write something that has to be portable. For now, in a SQL Server only shop, I'll opt for using the trimmer version, considering it gets used so many times.
I see your point and it's a good one. I'm sure I'll continue using ISNULL, and when using Oracle I'll use DECODE, but I'll better appreciate why a Sr. SQL Consultant might do otherwise.
(Quite honestly; I had forgotten that ISNULL wasn't standard. The danger of being a single shop guy - need to get out more. Ergo my sudden appearence on this forum. :) )|||COALESCE blows the socks off ISNULL when there are more than two terms in the list ;)
likely faster? you cannot say that without extensive benchmarking, can you
and what's a "smaller Object" -- is that some kind of object-oriented thingy? in which context would you need to measure this object size?
i wasn't aware that the compiled execution plan would actually be bigger for one function in a query as compared to another|||COALESCE blows the socks off ISNULL when there are more than two terms in the list ;)
likely faster? you cannot say that without extensive benchmarking, can you
and what's a "smaller Object" -- is that some kind of object-oriented thingy? in which context would you need to measure this object size?
i wasn't aware that the compiled execution plan would actually be bigger for one function in a query as compared to another
Yes, object oriented thingy. General overhead thing. Use the minimum.
Extensive benchmarking - I'd rather just take an educated guess and say "probably".
Why would MS make it if not to optimize things a little? If they just did it for readability, and they actually made it slower, well ... that's just very unlikely. If I said "probably blow's it's socks off performance wise", now that would be an irresponsible statement. I'd stand by my original statmenet and say that common sense (and many years of programming experience) suffice for a "probably".
"more than two terms in the list", well, that would be a different capability, more in line what that extra code is ment to handle. I think "blow socks off" is a misnomer. It's more binary than that, like SELECT vs. SET, since ISNULL does not accept multiple terms.
Anyway; I didn't mean to start a shouting match. I acknowledge that you're probably far senior to myself in such matters and was ernestly looking for an insight. Your point is well taken and I believe it's a valid one.
Cheers :beer:|||Why would MS make it if not to optimize things a little? oh! oh! i know this one! to be compatible with sql standards, maybe?
but thanks for the followup, and rest assured, i wasn't shouting
:cool:|||oh! oh! i know this one! to be compatible with sql standards, maybe?
but thanks for the followup, and rest assured, i wasn't shouting
:cool:
See, now I'm curious about the overhead.
Indeed, why would MS deviate from ANSI standard on this?
My general project management experience tells me someone made a hit-list of "how to optimize, how to simplify". However; was it part of a marketing conspiracy to prevent SQL Server shops from migrating out? haha.
If the former, then the gain would have to be substantial to justify it - or at least very easy to implement with a modest gain.
Guess someone with an IN to the MS development team, or a very in depth book on SQL Server "improvements" would have to answer that one. Or like you say, develop a benchmark. Honestly; I'm not that curious.|||Another deviation from ANSI SQL is the GROUP BY ALL (see BOL). Works OK if all you need to deal with is SQL Server.|||"more than two terms in the list", well, that would be a different capability, more in line what that extra code is ment to handle. I think "blow socks off" is a misnomer.i should have been a bit more explicit
COALESCE is easier to write, to understand, and to maintain ( = "blows the socks off") than a series of nested ISNULLs, when what you need to do is select the first non-null value in a series of values|||Which also means, thaty you would have to use ISNULL many time to mimic the COALESCE function, so performance point goes out the window
Also, I stay as ANSI as possible to because I work on many different database platforms...it's tough enough changing gears as it is
Man I hate DB2 OS/390 a lot these days|||ooohhhh sigh.....
always stick to the ANSI-92 as close as possible. My hands are not clean in this matter. I have wrtten some proprietary junk.
screw having to port something to oracle or mysql or whatever. ask people what they think about moving to MS SQL 2005 that have a bunch of *= and =* in their code instead of nice and proper OUTER JOIN statements.|||I absolutely love COALESCE!!! My shop here (and pretty much all new SQL coders I have seen in the past 4 years) take to ISNULL as if it were a heated jockstrap on a backpacking trip to Eagle Lake (Sierras) in early March!!!
I just love COALESCE. If I wasn't married, I would marry COALESCE. I have cleaned up much stacked, squeezed, haywired, and Elmer's Glued ISNULL nests with a single COALESCE that, in it's own sublime beauty, brings tears to the eyes of the SQL youngsters.
Put simply, I feel strongly that COALESCE is cleverer than ISNULL. I think COALESCE could whip ISNULL's butt in a towel-snapping fight, and in most recognized games of chance available in the world today.
I had no idea that ISNULL is not ANSII standard though, so I am thrilled to have read this thread. It's really about time that COALESCE got the press it deserves.
If I have not mentioned it, it is, in fact, probably my favorite verb in MS SQL.|||Guess someone with an IN to the MS development team, or a very in depth book on SQL Server "improvements" would have to answer that one.
Friend of friend was a dev on the sql server team <wink>. Didn't code up the ISNULL feature though, so can't speak with authority on its origins :)
Indeed, why would MS deviate from ANSI standard on this?
I am pretty sure that ISNULL did not come about because of some MS conspiracy to try to get people to move away from existing ANSI standards.
More likely is that ISNULL was the pet feature of some long lost PM. Fair bet that he/she wasn't even aware of the existence of COALESCE when they came up with it. :)|||I don't know of any benchmarks showing one is faster than the other. The difference would be less than negligible anyway.
Why did MS create ISNULL()? I don't know that they did. It was probably a part of SYBASE before Microsoft got a hold of it. Also suspicious that ISNULL() in MSSQL operates completely differently than ISNULL() in MSAccess, making it even more unlikely that it was Microsoft's idea.
If you have a choice between two methods and one is ANSI while the other is not, use the ANSI standard.|||Love finding such passion. Tall dude, careful what you wish for (marriage wise), lol.
Oracle has "nvl", SQL Server "isnull".
I'm opting for using stuff as appropriate. COALESCE just seems more like a parser of a string of potentially null values than a simple replacment converter. Like a conditional branch statement that has a conversion method as a side benifit. Just my impression.
Using it with a single arguement just seemed odd to me, but then, I've only used Microsoft and Oracle platforms.
Nesting ISNULL does seem messy. Wattya wanna bet (ok, nothing over 50 cents) that COALESCE actually uses ISNULL internally. Would certianly make sense, programming wise. Why repeat all that coding?
Anyway, hat's off to the "ANSI-92 Only" camp. I sure can't keep up with it.
Shame when we get spoiled with some Propietary feature. Main one I sorely miss is the PL SQL support for a "Cursor Loop". All the cursor's fields get pseudo created with a scope only within the loop, no extra definition required, and more importantly all the definitions and naming inhereted at compile time and therefore self-maintaining if someone comes along and changes a column definition. Yeah; what a time and code saver! I'll deviate any day for that one. I'm just an ignorant "only solve what's in front of me" guy so I have no idea if that's in the ANSI standard or not - or if SQL Server 2005 has something similar.|||SQL Server supports cursors?
I'm not the best one to answer that one...as the Great Sage has been saying for incalcuable millennia, "Nature Abhors A Cursor" (or something to that effect, anyway).
I have managed to use only two in the past 4 years...and both of those to demonstrate why they should NOT be used ;)
yeah, yeah, yeah...sometimes they are necessary (or it's not worth it to do it correctly for a one-off cursor solution), but I like to pretend they don't exist. Similar to the methodology I use to address the existence of the devil himself.|||COALESCE just seems more like a parser of a string of potentially null values than a simple replacment converter.
Nesting ISNULL does seem messy. Wattya wanna bet (ok, nothing over 50 cents) that COALESCE actually uses ISNULL internally. Would certianly make sense, programming wise. Why repeat all that coding?
Anyway, hat's off to the "ANSI-92 Only" camp. I sure can't keep up with it.
I wouldn't muddy ISNULL() the function with the concept of something being null. COALESCE and ISNULL are funtionally identical at the conceptual level for evaluating the first argument. COALESCE is not a parser by any stretch of the imagination. COALESCE just has infinantly more flexibility with a very small perceived performance hit.
Assume you have:
ISNULL(myScalarVal, myScalarResult)
and
COALESCE(myScalarVal, myScalarResult)
logically speaking, both functions must perform the exact same scalar comparison for null against myScalarVal. The difference is COALESCE will now have to perform the same scalar comparison against myScalarResult to determine if it is also null. This could be viewed as a minor performance hit, although scalar comparisons are about as trivial of an action as I can imagine.
Now assume we have:
ISNULL(myScalarVal, ISNULL(mySecondScalar, ISNULL(myThirdScalar, myScalarResult)))
and
COALESCE(myScalarVal, mySecondScalar, myThirdScalar, myScalarResult)
Now we have to execute three seperate statements, returning the result of each nested statement to its parent statement. There is inherint overhead in having multiple levels of recursion waiting on a return value. COALESCE by comparison does this intrinsically, not much caring how many values it's been given. It simply plods along looking for the first non-null value and throws away anything that doesn't satisfy this criteria as opposed to passing the failed results back to a calling function.
So all in all, I don't think there's much of a difference between the two for testing and substituting a single null value. However there are great benefits in performance, readibility and flexibility in using COALESCE for multi-element comparisons, so why not use COALESCE out of hand all the time?|||SQL Server supports cursors?
...
I have managed to use only two in the past 4 years...and both of those to demonstrate why they should NOT be used ;)
They're sure slow hua? Here I'm arguing over trivia and start talking about using something about 10 times slower. sigh. And 10 times clunkier to program.
Usually it's a conversion program or integrity check - but in production ... there's always some kind of alternative. Sometimes however, what the heck's the difference between .1 second and 2 seconds for a once a month process.
... That said, why not use COALESCE out of hand all the time?
Well, I'm sold. Quite honestly (I admit :eek: ), the answer is "harder to type and spell".|||Well, I'm sold. Quite honestly (I admit :eek: ), the answer is "harder to type and spell".Ahhh...but there is an advantage to that as well. Noob developers looking over shoulder think it is some mystical database arcana. Abra-cadabrac-coalesce...it helps keep up the reputation.|||My Main problem is still unanswered.
Problem statement:
If a select query without group by clause, returns null under column headers, if select query doesnt fetch any record.
If a select query with group by clause, not returns null under column headers, if select query doesnt fetch any record.
I want select query should return zero for int datatype and - for varchar datatype, if select query doesnt fetch any record.|||sorry, umeshm_patil, i don't think that's possible|||Indeed, why would MS deviate from ANSI standard on this?I [b]like[b] the easy questions... When Sybase created the IsNull function, Coalesce was still about seven years from coming into existance as a proposed addition to the standard. Microsoft inherited the SQL Server product with IsNull already well established.
Actually, starting in SQL 7.0 the IsNull function is now implemented internally as a call to the same routine that serves Coalesce. There might be minute difference in the Transact-SQL parsing time (only because Coalesce has more letters than IsNull), but that difference isn't material to the function execution. If there are more than two values (implying nested calls to IsNull), then a single call to Coalesce will win hands down.
-PatP|||My Main problem is still unanswered.
Problem statement:
If a select query without group by clause, returns null under column headers, if select query doesnt fetch any record.
If a select query with group by clause, not returns null under column headers, if select query doesnt fetch any record.
I want select query should return zero for int datatype and - for varchar datatype, if select query doesnt fetch any record.What you seem to be requesting is a formatter for "non values" in your result set. These are by definition application specific, they are not truly issues for the server to manage for you. They really ought to be handled in your application code to avoid pushing application presentation issues into the realm of your SQL Server.
-PatP|||I don't know of any benchmarks showing one is faster than the other. I know of three! One I have bookmarked (good old Adam) with links to the other two:
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/30/5311.aspx
The difference would be less than negligible anyway.Bingo!
Sorry for another <ot> post umeshm_patil. FWIW - I agree with Pat. Well done Pat!|||excellent link, pootle flump|||Nice!
A 4th test, (http://jerrytech.blogspot.com/2006/05/sql-2k-performance-isnull-vs-coalesce.html) linked by someone in the comment section of pootle flump's link, showed very surprising results.
The time for a nested ISNULL was only MARGINALLY longer than a single ISNULL.
Example:
9280ms : Set @.x = IsNull(Jerry, Nixon)
9296ms : Set @.x = IsNull(Jerry, IsNull(Nixon, Value))
9500ms : Set @.x = Coalesce(Jerry, Nixon)
9563ms : Set @.x = Coalesce(Jerry, Nixon, Value)
This test showed ISNULL to be slightly faster.
Hardly an arguement for using ISNULL given it's propietary nature, but it is an arguement for not rolling one's eyes quite so loudly when some programmer nests ISNULLs.|||btw, while we are on the topic of coalesce, C# has a coalesce operator now. I just learned about it following some of the perf testing links pootle_flump provided: http://weblogs.sqlteam.com/mladenp/archive/2006/03/27/9425.aspx
with it you can do this:
string foo = bar ?? "default value";
very neat! I love the operator because when I first saw it I thought "what??"|||<cough>
drop table testproducts
create table testproducts
(col1 int,
col2 varchar(10))
insert into testproducts
select 1, 'red'
union
select 2, 'yellow'
union
select 3, 'blue'
create table testorders
(col1 int,
col2 int)
insert into testorders
select 1, 10
union
select 2, 30
union
select 1, 50
union
select 2, 1
union
select 1, 5
-- Hey! No blue orders??
select sum(o.col2), count(*), p.col2
from testproducts p left outer join
testorders o on p.col1 = o.col1
group by all p.col2
Yeah, yeah. Group by all is non-ANSI, but it gets the OP through the day.|||<cough>something wrong with your throat?
guess what results you get for this query --select sum(o.col2), count(*), p.col2
from testproducts p left outer join
testorders o on p.col1 = o.col1
group by p.col2
so your point was... ?|||Mistaken, apparently.....|||Not to stray off topic, but anyone ever run COALESCE with NULL as the final value?
Interesting...|||Not to stray off topic, but anyone ever run COALESCE with NULL as the final value? i haven't
i don't think it's ever necessary, is it ;)|||i haven't
i don't think it's ever necessary, is it ;)
Of course not. It made for an interesting academic exercise in poking around behind how coalesce works though...|||Not to stray off topicI think off topic is the new on topic.
Of course not. It made for an interesting academic exercise in poking around behind how coalesce works though...Presumably this is where you got a lot of your conclusions from ealier? Do you have any code to hand or do we have to jig up our own? :)|||My earlier conclusions were nothing more than logical conjecture as a developer. It wasn't until all those contradictory perf studies were posted that I really wanted to start monkeying with this stuff.
Anyways, try these:
SELECT CASE WHEN 1=1 THEN NULL END
SELECT CASE WHEN 1=1 THEN NULLIF(1,1) END
SELECT COALESCE (NULL, NULL)
SELECT ISNULL(NULL, NULL)
This seems to suggest that ISNULL is using a different method of comparison then COALESCE. One that could very well be faster.|||Yeah, but this works...
DECLARE @.x int
SELECT COALESCE (NULL, @.x)
So perhaps it's just syntax and/or the use of the keyword NULL?|||The query engine has no way of knowing the runtime value of @.x when compiling the query plan. That's the same reason COALESCE(NULL, NULLIF(1,1)) will function properly; NULLIF() isn't evaluated until runtime. You can do the same trick using Case.
The conclusion I'm driving at is COALESCE() is nothing more than a wrapper for CASE whereas ISNULL() is doing something entirely different. This may explain why what would appear to be the logically superior structure for evaluating multiple elements isn't always the fastest. Now the question becomes:
Which is faster?
ISNULL(myScalar1, ISNULL(myScalar2, result))
or
CASE
WHEN myScalar1 <> NULL THEN myScalar1
WHEN myScalar2 <> NULL THEN myScalar2
ELSE result
END
Edit: A bit more co-noodling with another member here brought up an interesting opinion. COALESCE, being newer than ISNULL simply implements more stringent front-end validation as it is an ANSI standard. Now I'm all messed up... :(|||here's an example that came up today on another forum
scenario: there are two tables, persons and contacts, a person can have multiple contacts, each contact is a separate row with a contact type column, and the query is supposed to return each person with at most one contact -- return the person's email contact, and if email contact doesn't exist, return the work number, and if work number doesn't exist, return the home number
select p.name
, coalesce(e.contact
,w.contact
,h.contact) as contact
from persons as p
left outer
join contact as e
on e.personid = p.id
and e.contacttype = 'E' /* email */
left outer
join contact as w
on w.personid = p.id
and w.contacttype = 'W' /* work # */
left outer
join contact as h
on h.personid = p.id
and h.contacttype = 'H' /* home # */
Friday, March 9, 2012
Group by Restrictions?
Hi, Just a little doubt...
Is any difference or restriction there between using in the group by clause the columns as they are in the select list?
For example when I use an UPPER, CONVERT, etc.
Thanks : )
use pubs
--Case 1
select upper(title), type
from titles
group by upper(title), type
--Case 2
select upper(title), type
from titles
group by (title), type
In your case both the quires will be identical. (Upper & Lower)
When you use convert it may produce a different result..BUT YOU WONT GET ANY ERROR.
See the below sample,
Code Snippet
Create table #Test(
Date datetime,
StkQty int);
Insert into #Test Values('2007-01-01 10:00', 10)
Insert into #Test Values('2007-01-01 11:00', 100)
Insert into #Test Values('2007-01-02 12:00', 17)
Insert into #Test Values('2007-01-02 13:00', 13)
Select
Convert(varchar(10),Date,103)
,Sum(StkQty) as [Sum]
From
#test
Group By
Convert(varchar(10),Date,103)
/*
DateSum
01/01/2007 110
02/01/2007 30
*/
Select
Convert(varchar(10),Date,103)
,Sum(StkQty) as [Sum]
From
#test
Group By
Date
/*
DateSum
01/01/2007 10
01/01/2007 100
02/01/2007 17
02/01/2007 13
*/
|||
Yes I am sorry, it was a bad example. Suppose that I have made a select over some duplicated rows.
I need to make a kind of Catalog and want only distinct values, so as a way to do it I use the Group by Clause. Some times I use to apply "convert char", "case", "upper" over columns. I have seen some cases where Group By clause is showed exactly like the select list but just don't know the meaning.
Group by Problems
I know when you are using group by functions you have to include all
the columns in the GROUP BY clause.
the columns in the GROUP BY clause.
But what I am having problems when using a case statement to determine
whether to sum of not a column.
eg.
SELECT Country,
Case WHEN Age<15 THEN Sum(Income) ELSE NULL END AS YouthIncome,
Case WHEN Age>65 THEN Sum(Income) ELSE NULL END AS PensionIncome
FROM WORLDTABLE
GROUP BY Country
The problem is that this statement will not work as it says that Age
should be in the group by clause. But if include it, then there is no
point in using the SQL statement.
So is there another way to do this than using SELECT statements for
columns as I am worried that it will be inefficient and hog system
resources.Don't know if this is the best way (depends on number of records in
table WorldTable and number of users on it) but here is one thing you
can do...
SELECT
WT.Country
, ISNULL(T1.YouthIncome, NULL) AS YouthIncome
, ISNULL(T2.PensionIncome, NULL) AS PensionIncome
FROM
dbo.WorldTable AS WT
LEFT OUTER JOIN (
SELECT
W1.Country
, SUM(W1.Income) AS YouthIncome
FROM
dbo.WorldTable AS W1
WHERE
W1.Age < 15
GROUP BY
W1.Country )
AS T1 ON WT.Country = T1.Country
LEFT OUTER JOIN (
SELECT
W2.Country
, SUM(W2.Income) AS PensionIncome
FROM
dbo.WorldTable AS W2
WHERE
W2.Age > 65
GROUP BY
W2.Country )
AS T2 ON WT.Country = T2.Country
ORDER BY
WT.Country|||ree32@.hotmail.com (ree32) wrote in message news:<7606ccc8.0502222025.651a643f@.posting.google.com>...
> I know when you are using group by functions you have to include all
> the columns in the GROUP BY clause.
> But what I am having problems when using a case statement to determine
> whether to sum of not a column.
> eg.
> SELECT Country,
> Case WHEN Age<15 THEN Sum(Income) ELSE NULL END AS YouthIncome,
> Case WHEN Age>65 THEN Sum(Income) ELSE NULL END AS PensionIncome
> FROM WORLDTABLE
> GROUP BY Country
>
> The problem is that this statement will not work as it says that Age
> should be in the group by clause. But if include it, then there is no
> point in using the SQL statement.
> So is there another way to do this than using SELECT statements for
> columns as I am worried that it will be inefficient and hog system
> resources.
Try this
SELECT Country,sum(YouthIncome) as YouthIncome,sum(PensionIncome) as PensionIncome
FROM (
SELECT Country,
Case WHEN Age<15 THEN Income ELSE NULL END AS YouthIncome,
Case WHEN Age>65 THEN Income ELSE NULL END AS PensionIncome
FROM WORLDTABLE) as Deriv
GROUP BY Country|||SELECT country,
SUM(CASE WHEN age<15 THEN income END) AS YouthIncome,
SUM(CASE WHEN age>65 THEN income END) AS PensionIncome
FROM WORLDTABLE
GROUP BY Country
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<1109168754.792871.91720@.l41g2000cwc.googlegroups.c om>...
> SELECT country,
> SUM(CASE WHEN age<15 THEN income END) AS YouthIncome,
> SUM(CASE WHEN age>65 THEN income END) AS PensionIncome
> FROM WORLDTABLE
> GROUP BY Country
Thanks this is what I was looking for. Nice and easy.
Other posters thanks too.
Wednesday, March 7, 2012
Group by is not the same as MySQL
Group by is not the same as MySQL
I am migrating some of the query from MySQL to MS-SQL. I am stump on group
by clause and I would need your help. It may not be possible in MS-SQL and I
would have to load the data into the temp file then get rest of the fields.
I have a query that will need 9 fields and two of them had sum() aggregate
and I want it to group by item_id only, not all 7 fields. In MySQL, it would
sum up all the values and group it by item_id and still give me the values
for other fields that are not contained in an aggregate function.
Below is what my query statement looks like.
Thanks,
Grant
Select
item_list.item_id,
item_list.item_abrv,
item_list.item_desc,
ing.purch_unit_desc,
ing.stock_unit_desc,
ing.source_code,
ing.stock_unit_per,
sum(cost_physical_count.mkt_purch_qty) as mkt_purch_qt,
sum(cost_physical_count.mkt_stock_qty) as mkt_stock_qt,
from
ing,
item_nut,
item_list,
cost_physical_count
where
item_list.item_id = cost_physical_count.item_id
and item_list.item_id = ing.ing_id
and item_nut.item_id = ing.ing_id
group by item_list.item_id
order by item_list.item_descWhat values you want to see in following coulmns:
item_list.item_abrv,
item_list.item_desc,
ing.purch_unit_desc,
ing.stock_unit_desc,
ing.source_code,
ing.stock_unit_per
you would have summary of the rest 2 and group by item_id,
now which values you want to see in a columns above if there would be
multiple ? You can for example either use aggregates Max, Min, or you can
group by
these fields as well, but you should tell server which particular value
you want to choose.
Regards.
"UGH" wrote:
> Group by is not the same as MySQL
>
> I am migrating some of the query from MySQL to MS-SQL. I am stump on group
> by clause and I would need your help. It may not be possible in MS-SQL and I
> would have to load the data into the temp file then get rest of the fields.
>
> I have a query that will need 9 fields and two of them had sum() aggregate
> and I want it to group by item_id only, not all 7 fields. In MySQL, it would
> sum up all the values and group it by item_id and still give me the values
> for other fields that are not contained in an aggregate function.
>
> Below is what my query statement looks like.
> Thanks,
> Grant
>
>
> Select
> item_list.item_id,
> item_list.item_abrv,
> item_list.item_desc,
> ing.purch_unit_desc,
> ing.stock_unit_desc,
> ing.source_code,
> ing.stock_unit_per,
> sum(cost_physical_count.mkt_purch_qty) as mkt_purch_qt,
> sum(cost_physical_count.mkt_stock_qty) as mkt_stock_qt,
> from
> ing,
> item_nut,
> item_list,
> cost_physical_count
> where
> item_list.item_id = cost_physical_count.item_id
> and item_list.item_id = ing.ing_id
> and item_nut.item_id = ing.ing_id
> group by item_list.item_id
> order by item_list.item_desc
>
>|||Your query isn't legal in Standard SQL. I guess that MySQL fudges the
results by returning an undefined and potentially unpredictable set of
values for the columns that you didn't GROUP BY. This is a bug/feature that
appears in a few databases but it can be dangerous because it can lead to
inconsistent results.
To put it right we'll need a better spec: DDL, sample data, required end
results.
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||Thank you. There is no need for me to do the DDL thing. I will just query
the info into temp table then join the temp table with the other table to
get all fields that was not part of the aggravated functions.
Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ueFDj%23iYFHA.4032@.tk2msftngp13.phx.gbl...
> Your query isn't legal in Standard SQL. I guess that MySQL fudges the
> results by returning an undefined and potentially unpredictable set of
> values for the columns that you didn't GROUP BY. This is a bug/feature
> that appears in a few databases but it can be dangerous because it can
> lead to inconsistent results.
> To put it right we'll need a better spec: DDL, sample data, required end
> results.
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>|||On Fri, 27 May 2005 07:45:28 -0500, UGH wrote:
>Thank you. There is no need for me to do the DDL thing. I will just query
>the info into temp table then join the temp table with the other table to
>get all fields that was not part of the aggravated functions.
Hi UGH,
Why would you want to create a solution that needs more code and that
will execute slower?
If I look at your query, then the column names SUGGEST that in each
group, all values of item_abrv, item_desc, etc will always all be the
same. (If you had done "the DDL thing", I'd have known for sure...) Your
proposed temp table solution suggests the same.
If all values in the group will always be the same, you can pick just
any aggregate function to satisfy the requirements for a GROUP BY query:
SELECT item_list.item_id,
MIN(item_list.item_abrv) AS item_abrv,
MIN(item_list.item_desc) AS item_desc,
...
GROUP BY item_list.item_id
ORDER BY item_desc
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
I am migrating some of the query from MySQL to MS-SQL. I am stump on group
by clause and I would need your help. It may not be possible in MS-SQL and I
would have to load the data into the temp file then get rest of the fields.
I have a query that will need 9 fields and two of them had sum() aggregate
and I want it to group by item_id only, not all 7 fields. In MySQL, it would
sum up all the values and group it by item_id and still give me the values
for other fields that are not contained in an aggregate function.
Below is what my query statement looks like.
Thanks,
Grant
Select
item_list.item_id,
item_list.item_abrv,
item_list.item_desc,
ing.purch_unit_desc,
ing.stock_unit_desc,
ing.source_code,
ing.stock_unit_per,
sum(cost_physical_count.mkt_purch_qty) as mkt_purch_qt,
sum(cost_physical_count.mkt_stock_qty) as mkt_stock_qt,
from
ing,
item_nut,
item_list,
cost_physical_count
where
item_list.item_id = cost_physical_count.item_id
and item_list.item_id = ing.ing_id
and item_nut.item_id = ing.ing_id
group by item_list.item_id
order by item_list.item_descWhat values you want to see in following coulmns:
item_list.item_abrv,
item_list.item_desc,
ing.purch_unit_desc,
ing.stock_unit_desc,
ing.source_code,
ing.stock_unit_per
you would have summary of the rest 2 and group by item_id,
now which values you want to see in a columns above if there would be
multiple ? You can for example either use aggregates Max, Min, or you can
group by
these fields as well, but you should tell server which particular value
you want to choose.
Regards.
"UGH" wrote:
> Group by is not the same as MySQL
>
> I am migrating some of the query from MySQL to MS-SQL. I am stump on group
> by clause and I would need your help. It may not be possible in MS-SQL and I
> would have to load the data into the temp file then get rest of the fields.
>
> I have a query that will need 9 fields and two of them had sum() aggregate
> and I want it to group by item_id only, not all 7 fields. In MySQL, it would
> sum up all the values and group it by item_id and still give me the values
> for other fields that are not contained in an aggregate function.
>
> Below is what my query statement looks like.
> Thanks,
> Grant
>
>
> Select
> item_list.item_id,
> item_list.item_abrv,
> item_list.item_desc,
> ing.purch_unit_desc,
> ing.stock_unit_desc,
> ing.source_code,
> ing.stock_unit_per,
> sum(cost_physical_count.mkt_purch_qty) as mkt_purch_qt,
> sum(cost_physical_count.mkt_stock_qty) as mkt_stock_qt,
> from
> ing,
> item_nut,
> item_list,
> cost_physical_count
> where
> item_list.item_id = cost_physical_count.item_id
> and item_list.item_id = ing.ing_id
> and item_nut.item_id = ing.ing_id
> group by item_list.item_id
> order by item_list.item_desc
>
>|||Your query isn't legal in Standard SQL. I guess that MySQL fudges the
results by returning an undefined and potentially unpredictable set of
values for the columns that you didn't GROUP BY. This is a bug/feature that
appears in a few databases but it can be dangerous because it can lead to
inconsistent results.
To put it right we'll need a better spec: DDL, sample data, required end
results.
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||Thank you. There is no need for me to do the DDL thing. I will just query
the info into temp table then join the temp table with the other table to
get all fields that was not part of the aggravated functions.
Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ueFDj%23iYFHA.4032@.tk2msftngp13.phx.gbl...
> Your query isn't legal in Standard SQL. I guess that MySQL fudges the
> results by returning an undefined and potentially unpredictable set of
> values for the columns that you didn't GROUP BY. This is a bug/feature
> that appears in a few databases but it can be dangerous because it can
> lead to inconsistent results.
> To put it right we'll need a better spec: DDL, sample data, required end
> results.
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>|||On Fri, 27 May 2005 07:45:28 -0500, UGH wrote:
>Thank you. There is no need for me to do the DDL thing. I will just query
>the info into temp table then join the temp table with the other table to
>get all fields that was not part of the aggravated functions.
Hi UGH,
Why would you want to create a solution that needs more code and that
will execute slower?
If I look at your query, then the column names SUGGEST that in each
group, all values of item_abrv, item_desc, etc will always all be the
same. (If you had done "the DDL thing", I'd have known for sure...) Your
proposed temp table solution suggests the same.
If all values in the group will always be the same, you can pick just
any aggregate function to satisfy the requirements for a GROUP BY query:
SELECT item_list.item_id,
MIN(item_list.item_abrv) AS item_abrv,
MIN(item_list.item_desc) AS item_desc,
...
GROUP BY item_list.item_id
ORDER BY item_desc
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Group by is not the same as MySQL
Group by is not the same as MySQL
I am migrating some of the query from MySQL to MS-SQL. I am stump on group
by clause and I would need your help. It may not be possible in MS-SQL and I
would have to load the data into the temp file then get rest of the fields.
I have a query that will need 9 fields and two of them had sum() aggregate
and I want it to group by item_id only, not all 7 fields. In MySQL, it would
sum up all the values and group it by item_id and still give me the values
for other fields that are not contained in an aggregate function.
Below is what my query statement looks like.
Thanks,
Grant
Select
item_list.item_id,
item_list.item_abrv,
item_list.item_desc,
ing.purch_unit_desc,
ing.stock_unit_desc,
ing.source_code,
ing.stock_unit_per,
sum(cost_physical_count.mkt_purch_qty) as mkt_purch_qt,
sum(cost_physical_count.mkt_stock_qty) as mkt_stock_qt,
from
ing,
item_nut,
item_list,
cost_physical_count
where
item_list.item_id = cost_physical_count.item_id
and item_list.item_id = ing.ing_id
and item_nut.item_id = ing.ing_id
group by item_list.item_id
order by item_list.item_desc
What values you want to see in following coulmns:
item_list.item_abrv,
item_list.item_desc,
ing.purch_unit_desc,
ing.stock_unit_desc,
ing.source_code,
ing.stock_unit_per
you would have summary of the rest 2 and group by item_id,
now which values you want to see in a columns above if there would be
multiple ? You can for example either use aggregates Max, Min, or you can
group by
these fields as well, but you should tell server which particular value
you want to choose.
Regards.
"UGH" wrote:
> Group by is not the same as MySQL
>
> I am migrating some of the query from MySQL to MS-SQL. I am stump on group
> by clause and I would need your help. It may not be possible in MS-SQL and I
> would have to load the data into the temp file then get rest of the fields.
>
> I have a query that will need 9 fields and two of them had sum() aggregate
> and I want it to group by item_id only, not all 7 fields. In MySQL, it would
> sum up all the values and group it by item_id and still give me the values
> for other fields that are not contained in an aggregate function.
>
> Below is what my query statement looks like.
> Thanks,
> Grant
>
>
> Select
> item_list.item_id,
> item_list.item_abrv,
> item_list.item_desc,
> ing.purch_unit_desc,
> ing.stock_unit_desc,
> ing.source_code,
> ing.stock_unit_per,
> sum(cost_physical_count.mkt_purch_qty) as mkt_purch_qt,
> sum(cost_physical_count.mkt_stock_qty) as mkt_stock_qt,
> from
> ing,
> item_nut,
> item_list,
> cost_physical_count
> where
> item_list.item_id = cost_physical_count.item_id
> and item_list.item_id = ing.ing_id
> and item_nut.item_id = ing.ing_id
> group by item_list.item_id
> order by item_list.item_desc
>
>
|||Your query isn't legal in Standard SQL. I guess that MySQL fudges the
results by returning an undefined and potentially unpredictable set of
values for the columns that you didn't GROUP BY. This is a bug/feature that
appears in a few databases but it can be dangerous because it can lead to
inconsistent results.
To put it right we'll need a better spec: DDL, sample data, required end
results.
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
|||Thank you. There is no need for me to do the DDL thing. I will just query
the info into temp table then join the temp table with the other table to
get all fields that was not part of the aggravated functions.
Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ueFDj%23iYFHA.4032@.tk2msftngp13.phx.gbl...
> Your query isn't legal in Standard SQL. I guess that MySQL fudges the
> results by returning an undefined and potentially unpredictable set of
> values for the columns that you didn't GROUP BY. This is a bug/feature
> that appears in a few databases but it can be dangerous because it can
> lead to inconsistent results.
> To put it right we'll need a better spec: DDL, sample data, required end
> results.
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>
|||On Fri, 27 May 2005 07:45:28 -0500, UGH wrote:
>Thank you. There is no need for me to do the DDL thing. I will just query
>the info into temp table then join the temp table with the other table to
>get all fields that was not part of the aggravated functions.
Hi UGH,
Why would you want to create a solution that needs more code and that
will execute slower?
If I look at your query, then the column names SUGGEST that in each
group, all values of item_abrv, item_desc, etc will always all be the
same. (If you had done "the DDL thing", I'd have known for sure...) Your
proposed temp table solution suggests the same.
If all values in the group will always be the same, you can pick just
any aggregate function to satisfy the requirements for a GROUP BY query:
SELECT item_list.item_id,
MIN(item_list.item_abrv) AS item_abrv,
MIN(item_list.item_desc) AS item_desc,
...
GROUP BY item_list.item_id
ORDER BY item_desc
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
I am migrating some of the query from MySQL to MS-SQL. I am stump on group
by clause and I would need your help. It may not be possible in MS-SQL and I
would have to load the data into the temp file then get rest of the fields.
I have a query that will need 9 fields and two of them had sum() aggregate
and I want it to group by item_id only, not all 7 fields. In MySQL, it would
sum up all the values and group it by item_id and still give me the values
for other fields that are not contained in an aggregate function.
Below is what my query statement looks like.
Thanks,
Grant
Select
item_list.item_id,
item_list.item_abrv,
item_list.item_desc,
ing.purch_unit_desc,
ing.stock_unit_desc,
ing.source_code,
ing.stock_unit_per,
sum(cost_physical_count.mkt_purch_qty) as mkt_purch_qt,
sum(cost_physical_count.mkt_stock_qty) as mkt_stock_qt,
from
ing,
item_nut,
item_list,
cost_physical_count
where
item_list.item_id = cost_physical_count.item_id
and item_list.item_id = ing.ing_id
and item_nut.item_id = ing.ing_id
group by item_list.item_id
order by item_list.item_desc
What values you want to see in following coulmns:
item_list.item_abrv,
item_list.item_desc,
ing.purch_unit_desc,
ing.stock_unit_desc,
ing.source_code,
ing.stock_unit_per
you would have summary of the rest 2 and group by item_id,
now which values you want to see in a columns above if there would be
multiple ? You can for example either use aggregates Max, Min, or you can
group by
these fields as well, but you should tell server which particular value
you want to choose.
Regards.
"UGH" wrote:
> Group by is not the same as MySQL
>
> I am migrating some of the query from MySQL to MS-SQL. I am stump on group
> by clause and I would need your help. It may not be possible in MS-SQL and I
> would have to load the data into the temp file then get rest of the fields.
>
> I have a query that will need 9 fields and two of them had sum() aggregate
> and I want it to group by item_id only, not all 7 fields. In MySQL, it would
> sum up all the values and group it by item_id and still give me the values
> for other fields that are not contained in an aggregate function.
>
> Below is what my query statement looks like.
> Thanks,
> Grant
>
>
> Select
> item_list.item_id,
> item_list.item_abrv,
> item_list.item_desc,
> ing.purch_unit_desc,
> ing.stock_unit_desc,
> ing.source_code,
> ing.stock_unit_per,
> sum(cost_physical_count.mkt_purch_qty) as mkt_purch_qt,
> sum(cost_physical_count.mkt_stock_qty) as mkt_stock_qt,
> from
> ing,
> item_nut,
> item_list,
> cost_physical_count
> where
> item_list.item_id = cost_physical_count.item_id
> and item_list.item_id = ing.ing_id
> and item_nut.item_id = ing.ing_id
> group by item_list.item_id
> order by item_list.item_desc
>
>
|||Your query isn't legal in Standard SQL. I guess that MySQL fudges the
results by returning an undefined and potentially unpredictable set of
values for the columns that you didn't GROUP BY. This is a bug/feature that
appears in a few databases but it can be dangerous because it can lead to
inconsistent results.
To put it right we'll need a better spec: DDL, sample data, required end
results.
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
|||Thank you. There is no need for me to do the DDL thing. I will just query
the info into temp table then join the temp table with the other table to
get all fields that was not part of the aggravated functions.
Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ueFDj%23iYFHA.4032@.tk2msftngp13.phx.gbl...
> Your query isn't legal in Standard SQL. I guess that MySQL fudges the
> results by returning an undefined and potentially unpredictable set of
> values for the columns that you didn't GROUP BY. This is a bug/feature
> that appears in a few databases but it can be dangerous because it can
> lead to inconsistent results.
> To put it right we'll need a better spec: DDL, sample data, required end
> results.
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>
|||On Fri, 27 May 2005 07:45:28 -0500, UGH wrote:
>Thank you. There is no need for me to do the DDL thing. I will just query
>the info into temp table then join the temp table with the other table to
>get all fields that was not part of the aggravated functions.
Hi UGH,
Why would you want to create a solution that needs more code and that
will execute slower?
If I look at your query, then the column names SUGGEST that in each
group, all values of item_abrv, item_desc, etc will always all be the
same. (If you had done "the DDL thing", I'd have known for sure...) Your
proposed temp table solution suggests the same.
If all values in the group will always be the same, you can pick just
any aggregate function to satisfy the requirements for a GROUP BY query:
SELECT item_list.item_id,
MIN(item_list.item_abrv) AS item_abrv,
MIN(item_list.item_desc) AS item_desc,
...
GROUP BY item_list.item_id
ORDER BY item_desc
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Group by is not the same as MySQL
Group by is not the same as MySQL
I am migrating some of the query from mysql to MS-SQL. I am stump on group
by clause and I would need your help. It may not be possible in MS-SQL and I
would have to load the data into the temp file then get rest of the fields.
I have a query that will need 9 fields and two of them had sum() aggregate
and I want it to group by item_id only, not all 7 fields. In MySQL, it would
sum up all the values and group it by item_id and still give me the values
for other fields that are not contained in an aggregate function.
Below is what my query statement looks like.
Thanks,
Grant
Select
item_list.item_id,
item_list.item_abrv,
item_list.item_desc,
ing.purch_unit_desc,
ing.stock_unit_desc,
ing.source_code,
ing.stock_unit_per,
sum(cost_physical_count.mkt_purch_qty) as mkt_purch_qt,
sum(cost_physical_count.mkt_stock_qty) as mkt_stock_qt,
from
ing,
item_nut,
item_list,
cost_physical_count
where
item_list.item_id = cost_physical_count.item_id
and item_list.item_id = ing.ing_id
and item_nut.item_id = ing.ing_id
group by item_list.item_id
order by item_list.item_descWhat values you want to see in following coulmns:
item_list.item_abrv,
item_list.item_desc,
ing.purch_unit_desc,
ing.stock_unit_desc,
ing.source_code,
ing.stock_unit_per
you would have summary of the rest 2 and group by item_id,
now which values you want to see in a columns above if there would be
multiple ? You can for example either use aggregates Max, Min, or you can
group by
these fields as well, but you should tell server which particular value
you want to choose.
Regards.
"UGH" wrote:
> Group by is not the same as MySQL
>
> I am migrating some of the query from mysql to MS-SQL. I am stump on group
> by clause and I would need your help. It may not be possible in MS-SQL and
I
> would have to load the data into the temp file then get rest of the fields
.
>
> I have a query that will need 9 fields and two of them had sum() aggregate
> and I want it to group by item_id only, not all 7 fields. In MySQL, it wou
ld
> sum up all the values and group it by item_id and still give me the values
> for other fields that are not contained in an aggregate function.
>
> Below is what my query statement looks like.
> Thanks,
> Grant
>
>
> Select
> item_list.item_id,
> item_list.item_abrv,
> item_list.item_desc,
> ing.purch_unit_desc,
> ing.stock_unit_desc,
> ing.source_code,
> ing.stock_unit_per,
> sum(cost_physical_count.mkt_purch_qty) as mkt_purch_qt,
> sum(cost_physical_count.mkt_stock_qty) as mkt_stock_qt,
> from
> ing,
> item_nut,
> item_list,
> cost_physical_count
> where
> item_list.item_id = cost_physical_count.item_id
> and item_list.item_id = ing.ing_id
> and item_nut.item_id = ing.ing_id
> group by item_list.item_id
> order by item_list.item_desc
>
>|||Your query isn't legal in Standard SQL. I guess that mysql fudges the
results by returning an undefined and potentially unpredictable set of
values for the columns that you didn't GROUP BY. This is a bug/feature that
appears in a few databases but it can be dangerous because it can lead to
inconsistent results.
To put it right we'll need a better spec: DDL, sample data, required end
results.
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Thank you. There is no need for me to do the DDL thing. I will just query
the info into temp table then join the temp table with the other table to
get all fields that was not part of the aggravated functions.
Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ueFDj%23iYFHA.4032@.tk2msftngp13.phx.gbl...
> Your query isn't legal in Standard SQL. I guess that mysql fudges the
> results by returning an undefined and potentially unpredictable set of
> values for the columns that you didn't GROUP BY. This is a bug/feature
> that appears in a few databases but it can be dangerous because it can
> lead to inconsistent results.
> To put it right we'll need a better spec: DDL, sample data, required end
> results.
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>|||On Fri, 27 May 2005 07:45:28 -0500, UGH wrote:
>Thank you. There is no need for me to do the DDL thing. I will just query
>the info into temp table then join the temp table with the other table to
>get all fields that was not part of the aggravated functions.
Hi UGH,
Why would you want to create a solution that needs more code and that
will execute slower?
If I look at your query, then the column names SUGGEST that in each
group, all values of item_abrv, item_desc, etc will always all be the
same. (If you had done "the DDL thing", I'd have known for sure...) Your
proposed temp table solution suggests the same.
If all values in the group will always be the same, you can pick just
any aggregate function to satisfy the requirements for a GROUP BY query:
SELECT item_list.item_id,
MIN(item_list.item_abrv) AS item_abrv,
MIN(item_list.item_desc) AS item_desc,
...
GROUP BY item_list.item_id
ORDER BY item_desc
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
I am migrating some of the query from mysql to MS-SQL. I am stump on group
by clause and I would need your help. It may not be possible in MS-SQL and I
would have to load the data into the temp file then get rest of the fields.
I have a query that will need 9 fields and two of them had sum() aggregate
and I want it to group by item_id only, not all 7 fields. In MySQL, it would
sum up all the values and group it by item_id and still give me the values
for other fields that are not contained in an aggregate function.
Below is what my query statement looks like.
Thanks,
Grant
Select
item_list.item_id,
item_list.item_abrv,
item_list.item_desc,
ing.purch_unit_desc,
ing.stock_unit_desc,
ing.source_code,
ing.stock_unit_per,
sum(cost_physical_count.mkt_purch_qty) as mkt_purch_qt,
sum(cost_physical_count.mkt_stock_qty) as mkt_stock_qt,
from
ing,
item_nut,
item_list,
cost_physical_count
where
item_list.item_id = cost_physical_count.item_id
and item_list.item_id = ing.ing_id
and item_nut.item_id = ing.ing_id
group by item_list.item_id
order by item_list.item_descWhat values you want to see in following coulmns:
item_list.item_abrv,
item_list.item_desc,
ing.purch_unit_desc,
ing.stock_unit_desc,
ing.source_code,
ing.stock_unit_per
you would have summary of the rest 2 and group by item_id,
now which values you want to see in a columns above if there would be
multiple ? You can for example either use aggregates Max, Min, or you can
group by
these fields as well, but you should tell server which particular value
you want to choose.
Regards.
"UGH" wrote:
> Group by is not the same as MySQL
>
> I am migrating some of the query from mysql to MS-SQL. I am stump on group
> by clause and I would need your help. It may not be possible in MS-SQL and
I
> would have to load the data into the temp file then get rest of the fields
.
>
> I have a query that will need 9 fields and two of them had sum() aggregate
> and I want it to group by item_id only, not all 7 fields. In MySQL, it wou
ld
> sum up all the values and group it by item_id and still give me the values
> for other fields that are not contained in an aggregate function.
>
> Below is what my query statement looks like.
> Thanks,
> Grant
>
>
> Select
> item_list.item_id,
> item_list.item_abrv,
> item_list.item_desc,
> ing.purch_unit_desc,
> ing.stock_unit_desc,
> ing.source_code,
> ing.stock_unit_per,
> sum(cost_physical_count.mkt_purch_qty) as mkt_purch_qt,
> sum(cost_physical_count.mkt_stock_qty) as mkt_stock_qt,
> from
> ing,
> item_nut,
> item_list,
> cost_physical_count
> where
> item_list.item_id = cost_physical_count.item_id
> and item_list.item_id = ing.ing_id
> and item_nut.item_id = ing.ing_id
> group by item_list.item_id
> order by item_list.item_desc
>
>|||Your query isn't legal in Standard SQL. I guess that mysql fudges the
results by returning an undefined and potentially unpredictable set of
values for the columns that you didn't GROUP BY. This is a bug/feature that
appears in a few databases but it can be dangerous because it can lead to
inconsistent results.
To put it right we'll need a better spec: DDL, sample data, required end
results.
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Thank you. There is no need for me to do the DDL thing. I will just query
the info into temp table then join the temp table with the other table to
get all fields that was not part of the aggravated functions.
Thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ueFDj%23iYFHA.4032@.tk2msftngp13.phx.gbl...
> Your query isn't legal in Standard SQL. I guess that mysql fudges the
> results by returning an undefined and potentially unpredictable set of
> values for the columns that you didn't GROUP BY. This is a bug/feature
> that appears in a few databases but it can be dangerous because it can
> lead to inconsistent results.
> To put it right we'll need a better spec: DDL, sample data, required end
> results.
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>|||On Fri, 27 May 2005 07:45:28 -0500, UGH wrote:
>Thank you. There is no need for me to do the DDL thing. I will just query
>the info into temp table then join the temp table with the other table to
>get all fields that was not part of the aggravated functions.
Hi UGH,
Why would you want to create a solution that needs more code and that
will execute slower?
If I look at your query, then the column names SUGGEST that in each
group, all values of item_abrv, item_desc, etc will always all be the
same. (If you had done "the DDL thing", I'd have known for sure...) Your
proposed temp table solution suggests the same.
If all values in the group will always be the same, you can pick just
any aggregate function to satisfy the requirements for a GROUP BY query:
SELECT item_list.item_id,
MIN(item_list.item_abrv) AS item_abrv,
MIN(item_list.item_desc) AS item_desc,
...
GROUP BY item_list.item_id
ORDER BY item_desc
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Group by in a view can this be used ?
In a complex view the group by gives strange (wrong) results.
(Adding a group by and having clause generates more rows instead of less).
select A, B, C+ isnull(' '+D, '')+isnull(str(E), ''), F
from view_A
Results in : 720 rows
select A, B, C+ isnull(' '+D, '')+isnull(str(E), ''), F, count(*)
from view_A
group by A, B, C+ isnull(' '+D, '')+isnull(str(E), ''), F
having count(*) > 1
Results in : 33678 rows (a lot of them containing a 1 in the count(*)
column)
(There should be only 57 rows)
If the view is put into a table : select * into table_A from view_A
First result : 720
Second result : 57
Is this a (known) bug,
(Removing the count(*) from the first query results in a :
Server: Msg 8624, Level 16, State 16, Line 1
Internal SQL Server error.
)
The view uses union to get the results form 3 queries, which each have
several tables. There are no correlated subqueries.
ben brugmanPlease can you post some code to reproduce the problem: the DDL for the base
tables and the view (just the key columns and the columns involved in the
query will do) plus a small sample of data (post as INSERT statements).
--
David Portas
--
Please reply only to the newsgroup
--|||I could generate a sample, but I would have
to anominise all data and meta data (table, view and column names).
There is a number of tables involved and I would have
to create suetable data.
(Sorry my organisation does not allow me to do this otherwise).
But then it does take such a form that I do not expect anybody to
look at the problem. And it would take a considerable amount of time
to prepare this.
At the end of this message I have done this only for the views and the
offending queries.
(Just as an example to show that this is not very user friendly).
Thanks for your attention
ben brugman
LOOK AT THE EXAMPLE AT YOUR OWN PERIL.
/* View for a selection. */
CREATE VIEW dbo.View_S
AS
SELECT T157TABLE.F637FIELD,
T157TABLE.F687FIELD AS F346FIELD,
T157TABLE.F638FIELD,
'SE' AS F347FIELD,
T116TABLE.F280FIELD AS F345FIELD,
T104TABLE.F703FIELD AS F238FIELD,
T157TABLE.F652FIELD,
T157TABLE.F744FIELD,
T157TABLE.F745FIELD,
T116TABLE.F277FIELD AS F246FIELD,
T116TABLE.F278FIELD AS F342FIELD,
T157TABLE.F324FIELD, T157TABLE.F309FIELD,
T157TABLE.F311FIELD,
T157TABLE.F588FIELD,
T157TABLE.F590FIELD,
T157TABLE.F747FIELD
FROM T157TABLE INNER JOIN
T116TABLE ON
T116TABLE.F637FIELD = T157TABLE.F637FIELD AND
T116TABLE.F687FIELD = T157TABLE.F687FIELD
LEFT OUTER JOIN
T104TABLE ON
T104TABLE.F637FIELD = T157TABLE.F637FIELD AND
T104TABLE.F687FIELD = T157TABLE.F687FIELD
AND T104TABLE.F230FIELD = 'SIS'
/* The main View */
CREATE VIEW dbo.View_A
AS
SELECT T122TABLE.F637FIELD,
T122TABLE.F344FIELD AS F346FIELD,
T122TABLE.F638FIELD,
'CT' AS F347FIELD,
T122TABLE.F262FIELD AS F345FIELD,
T122TABLE.F238FIELD,
T122TABLE.F246FIELD, T122TABLE.F342FIELD,
T122TABLE.F324FIELD,
T122TABLE.F309FIELD,
IHCP_creator.F510FIELD AS F310FIELD,
T122TABLE.F588FIELD,
IHCP_mutator.F510FIELD AS F589FIELD,
T122TABLE.F747FIELD, NULL AS F652FIELD, NULL
AS F744FIELD, NULL
AS F745FIELD
FROM T122TABLE, T159TABLE IHCP_creator,
T159TABLE IHCP_mutator
WHERE T122TABLE.F311FIELD = IHCP_creator.F702FIELD
AND
T122TABLE.F590FIELD = IHCP_mutator.F702FIELD
UNION
SELECT T123TABLE.F637FIELD,
T123TABLE.F348FIELD AS F346FIELD,
T123TABLE.F638FIELD,
'TT' AS F347FIELD,
T123TABLE.F737FIELD AS F345FIELD,
T123TABLE.F238FIELD,
T123TABLE.F246FIELD,
T123TABLE.F342FIELD,
T123TABLE.F324FIELD,
T123TABLE.F309FIELD,
IHCP_creator.F510FIELD AS F310FIELD,
T123TABLE.F588FIELD,
IHCP_mutator.F510FIELD AS F589FIELD,
T123TABLE.F747FIELD, NULL
AS F652FIELD, NULL AS F744FIELD, NULL
AS F745FIELD
FROM T123TABLE,
T159TABLE IHCP_creator,
T159TABLE IHCP_mutator
WHERE T123TABLE.F311FIELD = IHCP_creator.F702FIELD
AND
T123TABLE.F590FIELD = IHCP_mutator.F702FIELD
UNION
SELECT View_S.F637FIELD,
View_S.F346FIELD,
View_S.F638FIELD,
View_S.F347FIELD,
View_S.F345FIELD,
View_S.F238FIELD,
View_S.F246FIELD,
View_S.F342FIELD,
View_S.F324FIELD,
View_S.F309FIELD,
IHCP_creator.F510FIELD AS F310FIELD,
View_S.F588FIELD,
IHCP_mutator.F510FIELD AS F589FIELD,
View_S.F747FIELD,
View_S.F652FIELD,
View_S.F744FIELD,
View_S.F745FIELD
FROM View_S,
T159TABLE IHCP_creator,
T159TABLE IHCP_mutator
WHERE View_S.F311FIELD = IHCP_creator.F702FIELD
AND
View_S.F590FIELD = IHCP_mutator.F702FIELD
/* The query which goes 'wrong' */
select F637FIELD, F638FIELD, F347FIELD+ isnull(' '+F744FIELD,
'')+isnull(str(F652FIELD), ''), F246FIELD, count(*) from View_A
group by F637FIELD, F638FIELD, F347FIELD+ isnull(' '+F744FIELD,
'')+isnull(str(F652FIELD), ''), F246FIELD
having count(*) > 1
/* The simple Query */
select F637FIELD, F638FIELD, F347FIELD+ isnull(' '+F744FIELD,
'')+isnull(str(F652FIELD), ''), F246FIELD from View_A
(Adding a group by and having clause generates more rows instead of less).
select A, B, C+ isnull(' '+D, '')+isnull(str(E), ''), F
from view_A
Results in : 720 rows
select A, B, C+ isnull(' '+D, '')+isnull(str(E), ''), F, count(*)
from view_A
group by A, B, C+ isnull(' '+D, '')+isnull(str(E), ''), F
having count(*) > 1
Results in : 33678 rows (a lot of them containing a 1 in the count(*)
column)
(There should be only 57 rows)
If the view is put into a table : select * into table_A from view_A
First result : 720
Second result : 57
Is this a (known) bug,
(Removing the count(*) from the first query results in a :
Server: Msg 8624, Level 16, State 16, Line 1
Internal SQL Server error.
)
The view uses union to get the results form 3 queries, which each have
several tables. There are no correlated subqueries.
ben brugmanPlease can you post some code to reproduce the problem: the DDL for the base
tables and the view (just the key columns and the columns involved in the
query will do) plus a small sample of data (post as INSERT statements).
--
David Portas
--
Please reply only to the newsgroup
--|||I could generate a sample, but I would have
to anominise all data and meta data (table, view and column names).
There is a number of tables involved and I would have
to create suetable data.
(Sorry my organisation does not allow me to do this otherwise).
But then it does take such a form that I do not expect anybody to
look at the problem. And it would take a considerable amount of time
to prepare this.
At the end of this message I have done this only for the views and the
offending queries.
(Just as an example to show that this is not very user friendly).
Thanks for your attention
ben brugman
LOOK AT THE EXAMPLE AT YOUR OWN PERIL.
/* View for a selection. */
CREATE VIEW dbo.View_S
AS
SELECT T157TABLE.F637FIELD,
T157TABLE.F687FIELD AS F346FIELD,
T157TABLE.F638FIELD,
'SE' AS F347FIELD,
T116TABLE.F280FIELD AS F345FIELD,
T104TABLE.F703FIELD AS F238FIELD,
T157TABLE.F652FIELD,
T157TABLE.F744FIELD,
T157TABLE.F745FIELD,
T116TABLE.F277FIELD AS F246FIELD,
T116TABLE.F278FIELD AS F342FIELD,
T157TABLE.F324FIELD, T157TABLE.F309FIELD,
T157TABLE.F311FIELD,
T157TABLE.F588FIELD,
T157TABLE.F590FIELD,
T157TABLE.F747FIELD
FROM T157TABLE INNER JOIN
T116TABLE ON
T116TABLE.F637FIELD = T157TABLE.F637FIELD AND
T116TABLE.F687FIELD = T157TABLE.F687FIELD
LEFT OUTER JOIN
T104TABLE ON
T104TABLE.F637FIELD = T157TABLE.F637FIELD AND
T104TABLE.F687FIELD = T157TABLE.F687FIELD
AND T104TABLE.F230FIELD = 'SIS'
/* The main View */
CREATE VIEW dbo.View_A
AS
SELECT T122TABLE.F637FIELD,
T122TABLE.F344FIELD AS F346FIELD,
T122TABLE.F638FIELD,
'CT' AS F347FIELD,
T122TABLE.F262FIELD AS F345FIELD,
T122TABLE.F238FIELD,
T122TABLE.F246FIELD, T122TABLE.F342FIELD,
T122TABLE.F324FIELD,
T122TABLE.F309FIELD,
IHCP_creator.F510FIELD AS F310FIELD,
T122TABLE.F588FIELD,
IHCP_mutator.F510FIELD AS F589FIELD,
T122TABLE.F747FIELD, NULL AS F652FIELD, NULL
AS F744FIELD, NULL
AS F745FIELD
FROM T122TABLE, T159TABLE IHCP_creator,
T159TABLE IHCP_mutator
WHERE T122TABLE.F311FIELD = IHCP_creator.F702FIELD
AND
T122TABLE.F590FIELD = IHCP_mutator.F702FIELD
UNION
SELECT T123TABLE.F637FIELD,
T123TABLE.F348FIELD AS F346FIELD,
T123TABLE.F638FIELD,
'TT' AS F347FIELD,
T123TABLE.F737FIELD AS F345FIELD,
T123TABLE.F238FIELD,
T123TABLE.F246FIELD,
T123TABLE.F342FIELD,
T123TABLE.F324FIELD,
T123TABLE.F309FIELD,
IHCP_creator.F510FIELD AS F310FIELD,
T123TABLE.F588FIELD,
IHCP_mutator.F510FIELD AS F589FIELD,
T123TABLE.F747FIELD, NULL
AS F652FIELD, NULL AS F744FIELD, NULL
AS F745FIELD
FROM T123TABLE,
T159TABLE IHCP_creator,
T159TABLE IHCP_mutator
WHERE T123TABLE.F311FIELD = IHCP_creator.F702FIELD
AND
T123TABLE.F590FIELD = IHCP_mutator.F702FIELD
UNION
SELECT View_S.F637FIELD,
View_S.F346FIELD,
View_S.F638FIELD,
View_S.F347FIELD,
View_S.F345FIELD,
View_S.F238FIELD,
View_S.F246FIELD,
View_S.F342FIELD,
View_S.F324FIELD,
View_S.F309FIELD,
IHCP_creator.F510FIELD AS F310FIELD,
View_S.F588FIELD,
IHCP_mutator.F510FIELD AS F589FIELD,
View_S.F747FIELD,
View_S.F652FIELD,
View_S.F744FIELD,
View_S.F745FIELD
FROM View_S,
T159TABLE IHCP_creator,
T159TABLE IHCP_mutator
WHERE View_S.F311FIELD = IHCP_creator.F702FIELD
AND
View_S.F590FIELD = IHCP_mutator.F702FIELD
/* The query which goes 'wrong' */
select F637FIELD, F638FIELD, F347FIELD+ isnull(' '+F744FIELD,
'')+isnull(str(F652FIELD), ''), F246FIELD, count(*) from View_A
group by F637FIELD, F638FIELD, F347FIELD+ isnull(' '+F744FIELD,
'')+isnull(str(F652FIELD), ''), F246FIELD
having count(*) > 1
/* The simple Query */
select F637FIELD, F638FIELD, F347FIELD+ isnull(' '+F744FIELD,
'')+isnull(str(F652FIELD), ''), F246FIELD from View_A
Sunday, February 26, 2012
GROUP by clause, /sub query problems
I'm trying to list salesreps (if they have any sales for a particular date) with their total sales amounts for a queried date, but when running this sql string in QueryAnalyzer, it says there is an error with syntax on Line 1 near "s" :
SELECT o .Rep_ID, o .ID, s.ID, SUM(b.orderamount) AS totalsales, b.order_ID
FROM (SELECT b.Deal_ID
FROM btransactions b
WHERE b.BoardDate = '20050815') SalesReps s INNER JOIN
orders o ON o .Rep_ID = s.ID INNER JOIN
b ON o.ID = b.Deal_ID
GROUP BY d .Rep_ID, d .ID, s.ID, b.order_ID
HAVING (SUM(b.orderamount) > 0)
?
.NetSports
Group By clause with an inserted column
Good afternoon all,
I'm trying to write SQL that adds a descriptive column and groups on that
column but I get an error saying my inserted column is invalid. Can anyone
help? An example follows.
Sales Table:
Sales Type Amount
A 5.00
A 6.00
B 2.00
SQL:
SELECT
(CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE SALES'
END), SUM(Amount)
GROUP BY '?
Desired Result:
TAXABLE SALES 11.00
NONTAXABLE SALES 2.00
Thanks in advance,
Don J> GROUP BY '?
CASE
WHEN Sales Type = 'A' THEN 'TAXABLE SALES'
ELSE 'NONTAXABLE SALES'
END
AMB
"Don Jellie" wrote:
> Good afternoon all,
> I'm trying to write SQL that adds a descriptive column and groups on that
> column but I get an error saying my inserted column is invalid. Can anyon
e
> help? An example follows.
> Sales Table:
> Sales Type Amount
> A 5.00
> A 6.00
> B 2.00
> SQL:
> SELECT
> (CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE SALES
'
> END), SUM(Amount)
> GROUP BY '?
> Desired Result:
> TAXABLE SALES 11.00
> NONTAXABLE SALES 2.00
> Thanks in advance,
> Don J|||Don Jellie wrote:
> Good afternoon all,
> I'm trying to write SQL that adds a descriptive column and groups on
> that column but I get an error saying my inserted column is invalid.
> Can anyone help? An example follows.
> Sales Table:
> Sales Type Amount
> A 5.00
> A 6.00
> B 2.00
> SQL:
> SELECT
> (CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE
> SALES' END), SUM(Amount)
> GROUP BY '?
> Desired Result:
> TAXABLE SALES 11.00
> NONTAXABLE SALES 2.00
> Thanks in advance,
> Don J
create table #a (SalesType char(1) NOT NULL, Amount DECIMAL(10, 2) NOT
NULL)
go
Insert Into #a Values ('A', 5.00)
Insert Into #a Values ('A', 6.00)
Insert Into #a Values ('B', 2.00)
go
SELECT
CASE
WHEN SalesType = 'A' THEN 'TAXABLE SALES'
ELSE 'NONTAXABLE SALES'
END,
SUM(Amount)
From
#a
GROUP BY SalesType
go
drop table #a
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Since your column named Sales Type has a space in it, you must "Quote" it by
wrapping it in " " or [ ] like so:
SELECT
CASE WHEN [Sales Type] = 'A'
THEN 'Taxable Sales'
ELSE 'NonTaxable Sales'
END AS "Sales Type",
SUM(Amount) AS "Amount"
GROUP BY [Sales Type]
"Don Jellie" <Jelliebean1@.msn.nospam.com> wrote in message
news:ADDCEAC9-659D-4EA2-8252-25E5DB0D6840@.microsoft.com...
> Good afternoon all,
> I'm trying to write SQL that adds a descriptive column and groups on that
> column but I get an error saying my inserted column is invalid. Can
anyone
> help? An example follows.
> Sales Table:
> Sales Type Amount
> A 5.00
> A 6.00
> B 2.00
> SQL:
> SELECT
> (CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE
SALES'
> END), SUM(Amount)
> GROUP BY '?
> Desired Result:
> TAXABLE SALES 11.00
> NONTAXABLE SALES 2.00
> Thanks in advance,
> Don J
I'm trying to write SQL that adds a descriptive column and groups on that
column but I get an error saying my inserted column is invalid. Can anyone
help? An example follows.
Sales Table:
Sales Type Amount
A 5.00
A 6.00
B 2.00
SQL:
SELECT
(CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE SALES'
END), SUM(Amount)
GROUP BY '?
Desired Result:
TAXABLE SALES 11.00
NONTAXABLE SALES 2.00
Thanks in advance,
Don J> GROUP BY '?
CASE
WHEN Sales Type = 'A' THEN 'TAXABLE SALES'
ELSE 'NONTAXABLE SALES'
END
AMB
"Don Jellie" wrote:
> Good afternoon all,
> I'm trying to write SQL that adds a descriptive column and groups on that
> column but I get an error saying my inserted column is invalid. Can anyon
e
> help? An example follows.
> Sales Table:
> Sales Type Amount
> A 5.00
> A 6.00
> B 2.00
> SQL:
> SELECT
> (CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE SALES
'
> END), SUM(Amount)
> GROUP BY '?
> Desired Result:
> TAXABLE SALES 11.00
> NONTAXABLE SALES 2.00
> Thanks in advance,
> Don J|||Don Jellie wrote:
> Good afternoon all,
> I'm trying to write SQL that adds a descriptive column and groups on
> that column but I get an error saying my inserted column is invalid.
> Can anyone help? An example follows.
> Sales Table:
> Sales Type Amount
> A 5.00
> A 6.00
> B 2.00
> SQL:
> SELECT
> (CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE
> SALES' END), SUM(Amount)
> GROUP BY '?
> Desired Result:
> TAXABLE SALES 11.00
> NONTAXABLE SALES 2.00
> Thanks in advance,
> Don J
create table #a (SalesType char(1) NOT NULL, Amount DECIMAL(10, 2) NOT
NULL)
go
Insert Into #a Values ('A', 5.00)
Insert Into #a Values ('A', 6.00)
Insert Into #a Values ('B', 2.00)
go
SELECT
CASE
WHEN SalesType = 'A' THEN 'TAXABLE SALES'
ELSE 'NONTAXABLE SALES'
END,
SUM(Amount)
From
#a
GROUP BY SalesType
go
drop table #a
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Since your column named Sales Type has a space in it, you must "Quote" it by
wrapping it in " " or [ ] like so:
SELECT
CASE WHEN [Sales Type] = 'A'
THEN 'Taxable Sales'
ELSE 'NonTaxable Sales'
END AS "Sales Type",
SUM(Amount) AS "Amount"
GROUP BY [Sales Type]
"Don Jellie" <Jelliebean1@.msn.nospam.com> wrote in message
news:ADDCEAC9-659D-4EA2-8252-25E5DB0D6840@.microsoft.com...
> Good afternoon all,
> I'm trying to write SQL that adds a descriptive column and groups on that
> column but I get an error saying my inserted column is invalid. Can
anyone
> help? An example follows.
> Sales Table:
> Sales Type Amount
> A 5.00
> A 6.00
> B 2.00
> SQL:
> SELECT
> (CASE WHEN Sales Type = 'A' THEN 'TAXABLE SALES ' ELSE 'NONTAXABLE
SALES'
> END), SUM(Amount)
> GROUP BY '?
> Desired Result:
> TAXABLE SALES 11.00
> NONTAXABLE SALES 2.00
> Thanks in advance,
> Don J
group by clause Query help
This is my query
select ano,max(date),a_subject from MY_TAB where table_name='xyz' and
ano=877
group by a_subject,ano order by a_subject
ANOmax(Date)A_Subject
8772005-01-20 00:00:00.000Subject_1
8771900-01-01 00:00:00.000Subject_2
8772004-12-20 00:00:00.000Subject_3
8772005-01-19 00:00:00.000Subject_4
-----------------------
When I put the status column in, it fetches all the rows.
select ano,max(date),a_subject,status from MY_TAB where
table_name='xyz' and ano=877 group by a_subject,ano,status order by
a_subject
ANOmax(Date)A_SubjectStatus
8772005-01-20 00:00:00.000Subject_1Not Started
8771900-01-01 00:00:00.000Subject_2Not Started
8772004-12-20 00:00:00.000Subject_3Completed
8771900-01-01 00:00:00.000Subject_3Not Started
8771900-01-01 00:00:00.000Subject_4Not Started
8772005-01-19 00:00:00.000Subject_4Not Started
----------------------
now what i want is
ANOmax(Date)A_SubjectStatus
8772005-01-20 00:00:00.000Subject_1Not Started
8771900-01-01 00:00:00.000Subject_2Not Started
8772004-12-20 00:00:00.000Subject_3Completed
8772005-01-19 00:00:00.000Subject_4Not Started
Thanks a lot for your help.
AJ[posted and mailed, please reply in news]
(aj70000@.hotmail.com) writes:
> select ano,max(date),a_subject from MY_TAB where table_name='xyz' and
> ano=877
> group by a_subject,ano order by a_subject
> ANO max(Date) A_Subject
> 877 2005-01-20 00:00:00.000 Subject_1
> 877 1900-01-01 00:00:00.000 Subject_2
> 877 2004-12-20 00:00:00.000 Subject_3
> 877 2005-01-19 00:00:00.000 Subject_4
> -----------------------
> When I put the status column in, it fetches all the rows.
> select ano,max(date),a_subject,status from MY_TAB where
> table_name='xyz' and ano=877 group by a_subject,ano,status order by
> a_subject
> ANO max(Date) A_Subject Status
> 877 2005-01-20 00:00:00.000 Subject_1 Not Started
> 877 1900-01-01 00:00:00.000 Subject_2 Not Started
> 877 2004-12-20 00:00:00.000 Subject_3 Completed
> 877 1900-01-01 00:00:00.000 Subject_3 Not Started
> 877 1900-01-01 00:00:00.000 Subject_4 Not Started
> 877 2005-01-19 00:00:00.000 Subject_4 Not Started
> ----------------------
> now what i want is
> ANO max(Date) A_Subject Status
> 877 2005-01-20 00:00:00.000 Subject_1 Not Started
> 877 1900-01-01 00:00:00.000 Subject_2 Not Started
> 877 2004-12-20 00:00:00.000 Subject_3 Completed
> 877 2005-01-19 00:00:00.000 Subject_4 Not Started
> Thanks a lot for your help.
With the example you have given, you would get away with:
select ano,max(date),a_subject, MIN(status)
from MY_TAB
where table_name='xyz' and ano=877
group by a_subject,ano
order by a_subject
But this does not work well, if you have Status values like "Anticipating"
which represents intermediate stages.
If you are looking for the status on the selected date, then this might be
better:
SELECT a.ano, a.maxdate, a.a_subject, b.status
FROM (select ano, maxdate, max(date), a_subject
from MY_TAB
where table_name='xyz' and ano=877
group by a_subject, ano) AS b
JOIN MY_TAB b ON a.ano = b.ano
AND a.maxdate = b.date
AND a.a_subject = b.a_subject
This assumes that ano/a_subject/date constitutes some form of key in
the table.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here is how a SELECT works in SQL ... at least in theory. Real
products will optimize things when they can.
a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors are
there. The table expression> AS <correlation name> option allows you
give a name to this working table which you then have to use for the
rest of the containing query.
b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (reject UNKNOWN and FALSE). The
WHERE clause is applied to the working set in the FROM clause.
c) Go to the optional GROUP BY clause, make groups and reduce each
group to a single row, replacing the original working table with the
new grouped table. The rows of a grouped table must be group
characteristics: (1) a grouping column (2) a statistic about the group
(i.e. aggregate functions) (3) a function or (4) an expression made up
those three items.
d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group.
e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions
in the SELECT are done after all the other clauses are done. The
"AS" operator can also give names to expressions in the SELECT
list. These new names come into existence all at once, but after the
WHERE clause, GROUP BY clause and HAVING clause has been executed; you
cannot use them in the SELECT list or the WHERE clause for that reason.
If there is a SELECT DISTINCT, then redundant duplicate rows are
removed. For purposes of defining a duplicate row, NULLs are treated
as matching (just like in the GROUP BY).
f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.
g) The ORDER BY clause is part of a cursor, not a query. The result
set is passed to the cursor, which can only see the names in the SELECT
clause list, and the sorting is done there. The ORDER BY clause cannot
have expression in it, or references to other columns because the
result set has been converted into a sequential file structure and that
is what is being sorted.
As you can see, things happen "all at once" in SQL, not from left to
right as they would in a sequential file/proceudral language model. In
those languages, these two statements produce different results:
READ (a, b, c) FROM File_X;
READ (c, a, b) FROM File_X;
while these two statements return the same data:
SELECT a, b, c FROM Table_X;
SELECT c, a, b FROM Table_X;
Think about what a confused mess this statement is in the SQL model.
SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar;
That is why such nonsense is illegal syntax.
GROUP BY Clause Problem.
I have a query that has a number of fields being returned from two tables (a
company table and a contact table), and I want to make sure that only one
record per company is returned. But when I run the query I get the
following error.
Column 'Contact.name' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
(As well as the same error for every other field in the query)
How can you work around this limitation? The DISTINCT clause isn't the
answer, because I could get multiple records with the same company but
different contacts at that company. I also want to try to avoid subqueries.
Thanks!!!
BobIf you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don=B4t post the query, its hard to help you.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Here is the Query, I am still getting the same error when I add a "token"
min() field.
SELECT Company.company_name, LEFT(Contact.name,20) AS NAME, Contact.state,
LEFT(Company.potential_number_of_users,10) AS USERS, Company.original_date,
Company.if_exported, dbo.displayphone(contact.phone_number_1) AS PHONE,
Contact.address_line_1, Contact.address_line_2, Contact.city, Contact.dear,
Contact.email_address, Contact.last_name, Contact.phone_number_1,
Contact.phone_number_fax, Contact.title, Contact.zip,
LEFT(RTRIM(contact.name)+', ' +company.company_name,50) AS COMPLETED_WITH,
MIN(Company.owner) AS GROUPCLAUSE FROM Company Inner Join Contact ON
Company.record_id = Contact.parent_id WHERE UPPER(Company.status) Like '0
LEAD%' And Company.call_back_date <= GETDATE() And Company.owner Like
'BOB%' GROUP BY Company.company_name ORDER BY Company.original_date DESC
What do you think?
Bob
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145988891.820648.197700@.u72g2000cwu.googlegroups.com...
If you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you dont post the query, its hard to help you.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Oh, do you mean min() on "each" displayed column? I'll give it a try...
Bob
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145988891.820648.197700@.u72g2000cwu.googlegroups.com...
If you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you dont post the query, its hard to help you.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Yes.
company table and a contact table), and I want to make sure that only one
record per company is returned. But when I run the query I get the
following error.
Column 'Contact.name' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
(As well as the same error for every other field in the query)
How can you work around this limitation? The DISTINCT clause isn't the
answer, because I could get multiple records with the same company but
different contacts at that company. I also want to try to avoid subqueries.
Thanks!!!
BobIf you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don=B4t post the query, its hard to help you.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Here is the Query, I am still getting the same error when I add a "token"
min() field.
SELECT Company.company_name, LEFT(Contact.name,20) AS NAME, Contact.state,
LEFT(Company.potential_number_of_users,10) AS USERS, Company.original_date,
Company.if_exported, dbo.displayphone(contact.phone_number_1) AS PHONE,
Contact.address_line_1, Contact.address_line_2, Contact.city, Contact.dear,
Contact.email_address, Contact.last_name, Contact.phone_number_1,
Contact.phone_number_fax, Contact.title, Contact.zip,
LEFT(RTRIM(contact.name)+', ' +company.company_name,50) AS COMPLETED_WITH,
MIN(Company.owner) AS GROUPCLAUSE FROM Company Inner Join Contact ON
Company.record_id = Contact.parent_id WHERE UPPER(Company.status) Like '0
LEAD%' And Company.call_back_date <= GETDATE() And Company.owner Like
'BOB%' GROUP BY Company.company_name ORDER BY Company.original_date DESC
What do you think?
Bob
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145988891.820648.197700@.u72g2000cwu.googlegroups.com...
If you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you dont post the query, its hard to help you.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Oh, do you mean min() on "each" displayed column? I'll give it a try...
Bob
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145988891.820648.197700@.u72g2000cwu.googlegroups.com...
If you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you dont post the query, its hard to help you.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Yes.
GROUP BY Clause Problem.
I have a query that has a number of fields being returned from two tables (a
company table and a contact table), and I want to make sure that only one
record per company is returned. But when I run the query I get the
following error.
Column 'Contact.name' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
(As well as the same error for every other field in the query)
How can you work around this limitation? The DISTINCT clause isn't the
answer, because I could get multiple records with the same company but
different contacts at that company. I also want to try to avoid subqueries.
Thanks!!!
BobIf you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don=B4t post the query, its hard to help you.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Here is the Query, I am still getting the same error when I add a "token"
min() field.
SELECT Company.company_name, LEFT(Contact.name,20) AS NAME, Contact.state,
LEFT(Company.potential_number_of_users,10) AS USERS, Company.original_date,
Company.if_exported, dbo.displayphone(contact.phone_number_1) AS PHONE,
Contact.address_line_1, Contact.address_line_2, Contact.city, Contact.dear,
Contact.email_address, Contact.last_name, Contact.phone_number_1,
Contact.phone_number_fax, Contact.title, Contact.zip,
LEFT(RTRIM(contact.name)+', ' +company.company_name,50) AS COMPLETED_WITH,
MIN(Company.owner) AS GROUPCLAUSE FROM Company Inner Join Contact ON
Company.record_id = Contact.parent_id WHERE UPPER(Company.status) Like '0
LEAD%' And Company.call_back_date <= GETDATE() And Company.owner Like
'BOB%' GROUP BY Company.company_name ORDER BY Company.original_date DESC
What do you think?
Bob
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145988891.820648.197700@.u72g2000cwu.googlegroups.com...
If you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don´t post the query, its hard to help you.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Oh, do you mean min() on "each" displayed column? I'll give it a try...
Bob
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145988891.820648.197700@.u72g2000cwu.googlegroups.com...
If you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don´t post the query, its hard to help you.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Yes.
company table and a contact table), and I want to make sure that only one
record per company is returned. But when I run the query I get the
following error.
Column 'Contact.name' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
(As well as the same error for every other field in the query)
How can you work around this limitation? The DISTINCT clause isn't the
answer, because I could get multiple records with the same company but
different contacts at that company. I also want to try to avoid subqueries.
Thanks!!!
BobIf you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don=B4t post the query, its hard to help you.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Here is the Query, I am still getting the same error when I add a "token"
min() field.
SELECT Company.company_name, LEFT(Contact.name,20) AS NAME, Contact.state,
LEFT(Company.potential_number_of_users,10) AS USERS, Company.original_date,
Company.if_exported, dbo.displayphone(contact.phone_number_1) AS PHONE,
Contact.address_line_1, Contact.address_line_2, Contact.city, Contact.dear,
Contact.email_address, Contact.last_name, Contact.phone_number_1,
Contact.phone_number_fax, Contact.title, Contact.zip,
LEFT(RTRIM(contact.name)+', ' +company.company_name,50) AS COMPLETED_WITH,
MIN(Company.owner) AS GROUPCLAUSE FROM Company Inner Join Contact ON
Company.record_id = Contact.parent_id WHERE UPPER(Company.status) Like '0
LEAD%' And Company.call_back_date <= GETDATE() And Company.owner Like
'BOB%' GROUP BY Company.company_name ORDER BY Company.original_date DESC
What do you think?
Bob
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145988891.820648.197700@.u72g2000cwu.googlegroups.com...
If you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don´t post the query, its hard to help you.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Oh, do you mean min() on "each" displayed column? I'll give it a try...
Bob
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145988891.820648.197700@.u72g2000cwu.googlegroups.com...
If you want to group the query and just need one, you will have to
apply at least on aggregate to the displayed columns, even if its MIN
or MAX, but unless you don´t post the query, its hard to help you.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Yes.
GROUP By clause or DISTINCT clause
Hi, can anyone shed some light on this issue?
SELECT Status from lupStatus
with a normal query it returns the correct recordcount
SELECT Status from lupStatus GROUP BY Status
but with a GROUP By clause or DISTINCT clause it return the recordcount
= -1Which application are you using? If it is VB then it shold be client
side cursor
Madhivanan|||Madhivanan, thanks for your response, we are not using client side
cursor, but server side (the query work fine, adLockReadonly but it
doesn't work when we use adLockOptimistic) VB COM|||Jacek (jack.pedzikiewicz@.gmail.com) writes:
> Madhivanan, thanks for your response, we are not using client side
> cursor, but server side (the query work fine, adLockReadonly but it
> doesn't work when we use adLockOptimistic) VB COM
So the count we are talking about is Recordset.RecordCount?
What cursor type do you ask for? A query with GROUP BY or DISTINCT
can only result in a static or forward-only cursor, and it cannot
be updatable. A static cursor has a rowcount, a forward-only cursor
has not.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Group By Clause Limitation
Code is:
select
case when ItemCode is null then '-'
else ItemCode
End,
case when sum(RecdQty) is null then '-'
else sum(RecdQty)
End
from ItemMaster where ItemCode='V001' group by ItemCode
Problem Statement:
If query is not getting any records for above mentioned condition, then I want zero to be displayed if datatype is int (i.e. for sum(RecdQty) field) and '-' to be diplayed if datatype is varchar (i.e. for ItemCode field).
In this situation, "ItemCode is null" and "sum(RecdQty) is null" conditions are not been utilised.
Is this a limitation of case or group by clause?No, this is not a limitation of SQL at all, it is doing exactly what it is supposed to do. Please see my explanation from the last time you asked this question by clicking here (http://www.dbforums.com/showthread.php?p=6237156#post6237156). If that explanation isn't clear or sufficient, please continue the discussion in that thread instead of starting new threads.
-PatP|||Consider this
select count(*) from master..sysdatabases
where 1=2
will return 0. But
select count(*) from master..sysdatabases
where 1=2
group by status
return no records
Now you want a specific ItemCode. There is no need for the group by
select isnull(min(ItemCode),'-')
,isnull(sum(RecdQty),0)
from ItemMaster
where ItemCode='V001'
select
case when ItemCode is null then '-'
else ItemCode
End,
case when sum(RecdQty) is null then '-'
else sum(RecdQty)
End
from ItemMaster where ItemCode='V001' group by ItemCode
Problem Statement:
If query is not getting any records for above mentioned condition, then I want zero to be displayed if datatype is int (i.e. for sum(RecdQty) field) and '-' to be diplayed if datatype is varchar (i.e. for ItemCode field).
In this situation, "ItemCode is null" and "sum(RecdQty) is null" conditions are not been utilised.
Is this a limitation of case or group by clause?No, this is not a limitation of SQL at all, it is doing exactly what it is supposed to do. Please see my explanation from the last time you asked this question by clicking here (http://www.dbforums.com/showthread.php?p=6237156#post6237156). If that explanation isn't clear or sufficient, please continue the discussion in that thread instead of starting new threads.
-PatP|||Consider this
select count(*) from master..sysdatabases
where 1=2
will return 0. But
select count(*) from master..sysdatabases
where 1=2
group by status
return no records
Now you want a specific ItemCode. There is no need for the group by
select isnull(min(ItemCode),'-')
,isnull(sum(RecdQty),0)
from ItemMaster
where ItemCode='V001'
Group By clause killing performance
I have recently started working with a new group of people and I find myself doing a lot of reporting. While doing this reporting I have been writing a TON of sql. Some of my queries were not performing up to par and another developer in the shop recommended that I stay away from the "GROUP BY" clause.
Backing away from the "GROUP BY" clause and using "INNER SELECTS" instead as been more effective and some queries have gone from over 1 minute to less that 1 second.
Obviously if it works then it works and there is no arguing that point. My question to the forum is more about gather some opinions so that I can build an opinion of my own.
If I cannot do a reasonable query of a couple of million records using a group by clause what is the problem and what is the best fix?
Is the best fix to remove the "GROUP BY" and write a query that is a little more complex or should I be looking at tuning the database with more indexes and statistics?
I want to make sure that this one point is crystal clear. I am not against following the advice of my coworker and avoiding the "GROUP BY" clause. I am only intersted in listening to a few others talk about why the agree or disagree with my coworked so that I can gain a broader understanding.
It is a combination of few factors.(1) It is imptant to have proper indexes on columns being queried in WHERE and GROUP BY (2) Also see if you can move the GROUP BY to the reporting tool? (3) Is it the GROUP BY thats killing it or something else? Are you using any functions on columns in the WHERE? like some CONVERT(Datecolumn,100) >= '2007/01/01' etc?
|||It is definetly the group by that is killing it. When the query was rewritten to to remove the group by the execution time dropped through the floor.
from 1 minute to less than 1 second.
Here is a sudo example of what I mean
old query first
SELECT
column1,
column2,
column3,
SUM(something)
FROM
table1
inner join table 2 on 1.columna = 2.columnb
GROUP BY
column1,
column2,
column3
new query
SELECT
column1,
column2,
column3,
(SELECT SUM(something) From sometable) AS 'blah'
FROM
table1
inner join table 2 on 1.columna = 2.columnb
I know that there huge gap between what is really going on and the code above but you get the main idea. moving the sum to a select so that the group by is no longer required. This and this allow drastically reduced the amount of time that it took to get the data. I knew that group by was expensive I just didn't realize how expensive it was.
|||The group by clause is not in itself a bad performer. There is something else at work but without more detail, I can't tell you what.
The two queries you gave aren't the same thing. The second query doesn't do a sum based on the contents of the current row (No where clause relating the two). Which then of course it runs much faster, it's only executing the sum once, and using it on every row of the outer query.
|||No mystery. "If I cannot do a reasonable query of a couple of million records..."
Sorting a couple of million records is, well, expensive! That's what a group by does, it sorts. And you don't even have a where clause to limit the answer set.
If you put a clustered index on column1, column2, column3 it will be able to avoid the sort, but you need to look at that carefully since it may have an impact on other queries (and you may already have a clustered index)
|||True, the second query is unsorted (Not that common to request a set of data and not care about it's sort order), which will obviously be a completely different query plan. I would venture to guess that you don't have a good index on the table either that can/will help you.
Instead of putting a clustered index on the table, if you put an index on column1,column2,column3 and the field you are summing, your query time will drop significantly as well.
Faster yet, would be to use an indexed view.
|||I am hearing basically what I thought I would hear. GROUP BY equals SORTING, a couple million records is a lot of data, no need to aviod GROUP BY like the plauge, check the indexes and statistics too.
Thanks. There is never a right or wrong answer to this kind of thing, it always depends on the shop and the database.
Group By Clause Help
Hello the code below shows multiple instances of targets.name "donor type" I could not correclty run the code without including contributions.program. I would like the output to only have 1 value for each donor type. How would I do this or workaround to get it done?
- thanks for your time.
SQL> SELECT targets.name "DONOR TYPE", contribution.program,
2 SUM(contribution.amount) "CONTRIBUTION QTR2"
3 FROM donor, contribution, targets
4 WHERE contribution.cdate >= TO_DATE('04/01/03', 'MM/DD/YY')
5 AND contribution.cdate <= TO_DATE('06/30/03', 'MM/DD/YY')
6 AND donor.donor = contribution.donor
7 AND targets.type = donor.type
8 GROUP BY targets.name, contributions.program;
DONOR TYPE PROGRAM CONTRIBUTION QTR2
------- -------- ------
Corporate Donors Applied Research 100
Foundations Applied Research 175
Individuals Basic Research 50
Corporate Donors International Programs 100
Corporate Donors Teaching Programs 50
Foundations Teaching Programs 50What prevents you from doing this?:
SQL> SELECT targets.name "DONOR TYPE",
2 SUM(contribution.amount) "CONTRIBUTION QTR2"
3 FROM donor, contribution, targets
4 WHERE contribution.cdate >= TO_DATE('04/01/03', 'MM/DD/YY')
5 AND contribution.cdate <= TO_DATE('06/30/03', 'MM/DD/YY')
6 AND donor.donor = contribution.donor
7 AND targets.type = donor.type
8 GROUP BY targets.name;
DONOR TYPE CONTRIBUTION QTR2
------- ------
Corporate Donors 250
Foundations 225
Individuals 50|||How can I get tthe output to look like this?
DONOR TYPE PROGRAM CONTRIBUTION QTR2
------- -------- ------
Corporate Donors Applied Research 100
International Programs 100
Teaching Programs 50
Foundations Applied Research 175
Teaching Programs 50
Individuals Basic Research 50
- thanks for your help|||Oh I see, you mean suppress the output of the repeated value?
In SQL Plus, use:
SQL> BREAK ON "DONOR TYPE"
Also, add "ORDER BY targets.name, contributions.program" after the GROUP BY clause to be sure the ordering is correct (GROUP BY doesn't guarantee the order).|||Great this worked for that table--thanks a bunch. How can I group by contrubuion by member only so there is only one isntance per name and the sum of all rows info and still maintain each of the columns. this can be easily done by removing the target column but I need to display it along with the others belwo. Once put the target column in I must also gruoup by member.qtr1 which produces the multiple row output. How can I work around this to only group by member?
SQL> SELECT contribution.member, member.qtr1 "TARGET",
2 SUM(contribution.amount) "CONT. QTR1",
3 ROUND(SUM(contribution.amount)/member.qtr1,3)*10 "% OF PROJECTION"
4 FROM contribution, member
5 WHERE contribution.cdate >= TO_DATE('01/01/03', 'MM/DD/YY')
6 AND contribution.cdate <= TO_DATE('03/31/03', 'MM/DD/YY')
7 GROUP BY contribution.member, member.qtr1;
MEMBER TARGET CONT. QTR1 % OF PROJECTION
----- ---- ---- -----
Adams 50 175 35
Adams 75 175 23.33
Adams 100 175 17.5
Adams 150 175 11.67
Adams 200 175 8.75
Adams 250 175 7
Baker 50 100 20
Baker 75 100 13.33
Baker 100 100 10
Baker 150 100 6.67
Baker 200 100 5
- thanks for your time.
SQL> SELECT targets.name "DONOR TYPE", contribution.program,
2 SUM(contribution.amount) "CONTRIBUTION QTR2"
3 FROM donor, contribution, targets
4 WHERE contribution.cdate >= TO_DATE('04/01/03', 'MM/DD/YY')
5 AND contribution.cdate <= TO_DATE('06/30/03', 'MM/DD/YY')
6 AND donor.donor = contribution.donor
7 AND targets.type = donor.type
8 GROUP BY targets.name, contributions.program;
DONOR TYPE PROGRAM CONTRIBUTION QTR2
------- -------- ------
Corporate Donors Applied Research 100
Foundations Applied Research 175
Individuals Basic Research 50
Corporate Donors International Programs 100
Corporate Donors Teaching Programs 50
Foundations Teaching Programs 50What prevents you from doing this?:
SQL> SELECT targets.name "DONOR TYPE",
2 SUM(contribution.amount) "CONTRIBUTION QTR2"
3 FROM donor, contribution, targets
4 WHERE contribution.cdate >= TO_DATE('04/01/03', 'MM/DD/YY')
5 AND contribution.cdate <= TO_DATE('06/30/03', 'MM/DD/YY')
6 AND donor.donor = contribution.donor
7 AND targets.type = donor.type
8 GROUP BY targets.name;
DONOR TYPE CONTRIBUTION QTR2
------- ------
Corporate Donors 250
Foundations 225
Individuals 50|||How can I get tthe output to look like this?
DONOR TYPE PROGRAM CONTRIBUTION QTR2
------- -------- ------
Corporate Donors Applied Research 100
International Programs 100
Teaching Programs 50
Foundations Applied Research 175
Teaching Programs 50
Individuals Basic Research 50
- thanks for your help|||Oh I see, you mean suppress the output of the repeated value?
In SQL Plus, use:
SQL> BREAK ON "DONOR TYPE"
Also, add "ORDER BY targets.name, contributions.program" after the GROUP BY clause to be sure the ordering is correct (GROUP BY doesn't guarantee the order).|||Great this worked for that table--thanks a bunch. How can I group by contrubuion by member only so there is only one isntance per name and the sum of all rows info and still maintain each of the columns. this can be easily done by removing the target column but I need to display it along with the others belwo. Once put the target column in I must also gruoup by member.qtr1 which produces the multiple row output. How can I work around this to only group by member?
SQL> SELECT contribution.member, member.qtr1 "TARGET",
2 SUM(contribution.amount) "CONT. QTR1",
3 ROUND(SUM(contribution.amount)/member.qtr1,3)*10 "% OF PROJECTION"
4 FROM contribution, member
5 WHERE contribution.cdate >= TO_DATE('01/01/03', 'MM/DD/YY')
6 AND contribution.cdate <= TO_DATE('03/31/03', 'MM/DD/YY')
7 GROUP BY contribution.member, member.qtr1;
MEMBER TARGET CONT. QTR1 % OF PROJECTION
----- ---- ---- -----
Adams 50 175 35
Adams 75 175 23.33
Adams 100 175 17.5
Adams 150 175 11.67
Adams 200 175 8.75
Adams 250 175 7
Baker 50 100 20
Baker 75 100 13.33
Baker 100 100 10
Baker 150 100 6.67
Baker 200 100 5
GROUP BY CLAUSE
Greetings!
Why does the select statement must have the same non aggregated columns which are also part of the group by clause?
I have a feeling it's to do with the way the SQL Server database engine actually executes the query? i.e. the select part is the last thing the engine performs (after doing the joins and the filtering etc...).
Your help would be appreciated.
Hi..
Yes.. you are right.. SQL Server performs Select Part is almost last.. hence you have to provide the columns that are there in group by clause as non aggregate columns in the select..
Here is a quick reference to the Select Process in sequencial..
Code Snippet
SelectFrom
Join
On
Where
Group By
With {Cube | Rollup}
Having
Order By
|||
Sequence
1. from <left table>
2. on <join condition>
3. <join type> join <right table>
4. where <where condition>
5. group by <fields list>
6. with {cube | rollup}
7. having <having condition>
8. Select
9. distinct
10. order by <field list>
11. <top spciancifications>Hope this helps..
MS already heard from you..
If you use SQL Server 2005, you can have other columns in your Select statement. Using OVER clause..
The query will look like this...
select *, Sum(Price) over (partition by orderId) from orders
Subscribe to:
Posts (Atom)