Grouping question

I have the following query:
SELECT PR_NO,
Total = CASE Items.Use_Item_Calc_Qty
WHEN 0 THEN CONVERT(money, SUM
(items.unit_price * items.qty))
ELSE CONVERT(money, SUM(items.unit_price *
items.qty * ITEM_CALC_QTY))
END
FROM Items
where pr_no = 5816
Group By PR_NO, Use_Item_Calc_Qty
The query is returning two records because a record in
the items table has a value of 0 in Use_item_calc_qty and
another record has a value of one.
What I want to return is only one record showing the
total for the Purchase Request. Can anyone help me with
this. I appreciate it.Vic,
I think this is what you wanted to do (your statement of problem is not
quite clear):
SELECT PR_NO,
Total = CONVERT(money, SUM(items.unit_price *
items.qty * (CASE Items.Use_Item_Calc_Qty when 0 then 1 else
Use_Item_Calc_Qty end)))
FROM Items
where pr_no = 5816
Group By PR_NO, Use_Item_Calc_Qty
hth
Quentin
"Vic" <vduran@.specpro-inc.com> wrote in message
news:000d01c3c0dc$56b27560$a501280a@.phx.gbl...
> I have the following query:
> SELECT PR_NO,
> Total => CASE Items.Use_Item_Calc_Qty
> WHEN 0 THEN CONVERT(money, SUM
> (items.unit_price * items.qty))
> ELSE CONVERT(money, SUM(items.unit_price *
> items.qty * ITEM_CALC_QTY))
> END
> FROM Items
> where pr_no = 5816
> Group By PR_NO, Use_Item_Calc_Qty
> The query is returning two records because a record in
> the items table has a value of 0 in Use_item_calc_qty and
> another record has a value of one.
> What I want to return is only one record showing the
> total for the Purchase Request. Can anyone help me with
> this. I appreciate it.sql

Group column name problem

How would you do this statement:
Select rank=count(*),
Case when ProductTypeID = 1 then j.ItemName when ProductTypeID = 2 then
r.ItemName end as Description,
Price, PurchaseQty, TotalPrice = Price * PurchaseQty
from PurchaseDetail pd
join PurchaseMaster pm on (pd.PurchaseMasterID = pm.PurchaseMasterID)
left JOIN JobPostingPrices j on (ProductID = JobPostingPriceID)
left JOIN ResumeAccessPrices r on (ProductID = ResumeAccessPriceID)
where CompanyID = 153973
group by Description,Price,PurchaseQty,TotalPrice
The problem is I get an "Invalid Column Name" for Description and
TotalPrice.
I assume that is because the names are assigned.
How can I make this work?
Thanks,
Tomgroup by the expression, e.g.
...group by Case when ProductTypeID = 1 then j.ItemName when
ProductTypeID = 2 then
r.ItemName end,
Price, PurchaseQty,
Price * PurchaseQty
tshad wrote:

>How would you do this statement:
>Select rank=count(*),
> Case when ProductTypeID = 1 then j.ItemName when ProductTypeID = 2 then
>r.ItemName end as Description,
> Price, PurchaseQty, TotalPrice = Price * PurchaseQty
>from PurchaseDetail pd
>join PurchaseMaster pm on (pd.PurchaseMasterID = pm.PurchaseMasterID)
>left JOIN JobPostingPrices j on (ProductID = JobPostingPriceID)
>left JOIN ResumeAccessPrices r on (ProductID = ResumeAccessPriceID)
>where CompanyID = 153973
>group by Description,Price,PurchaseQty,TotalPrice
>The problem is I get an "Invalid Column Name" for Description and
>TotalPrice.
>I assume that is because the names are assigned.
>How can I make this work?
>Thanks,
>Tom
>
>|||"Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
news:eAwOKUa2FHA.3600@.TK2MSFTNGP12.phx.gbl...
> group by the expression, e.g.
> ...group by Case when ProductTypeID = 1 then j.ItemName when ProductTypeID
> = 2 then
> r.ItemName end,
> Price, PurchaseQty,
> Price * PurchaseQty
>
I was hoping I wouldn't have to do that. That would mean in my larger
scripts that use large Case statements would also have to be put in the
Group by clause.
Thanks,
Tom
> tshad wrote:
>|||On Wed, 26 Oct 2005 16:36:03 -0700, tshad wrote:

>"Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
>news:eAwOKUa2FHA.3600@.TK2MSFTNGP12.phx.gbl...
>I was hoping I wouldn't have to do that. That would mean in my larger
>scripts that use large Case statements would also have to be put in the
>Group by clause.
Hi Tom,
There are two workarounds:
1. Instead of including the expression in the GROUP BY, include ALL
columns used in the expression. I've never checked if ANSI standard
allows it, but AFAIK, SQL Server does.
2. Use a derived table:
SELECT result, MAX(something else)
FROM (SELECT complicated expression AS result,
something else
FROM some tables
WHERE whatever you want) AS Der
GROUP BY result
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:p5g2m1pigre1nfievlll39mfp7nh9jdd94@.
4ax.com...
> On Wed, 26 Oct 2005 16:36:03 -0700, tshad wrote:
>
ProductTypeID
> Hi Tom,
> There are two workarounds:
> 1. Instead of including the expression in the GROUP BY, include ALL
> columns used in the expression. I've never checked if ANSI standard
> allows it, but AFAIK, SQL Server does.
Haven't tried that yet, but wouldn't the grouping be incorrect as you are
looking at the value of the column instead of the derived value of that
expression from the column?
Not really sure why you can't use the assigned title of the column( x as
column).

> 2. Use a derived table:
> SELECT result, MAX(something else)
> FROM (SELECT complicated expression AS result,
> something else
> FROM some tables
> WHERE whatever you want) AS Der
> GROUP BY result
That was also what Peter suggested, which worked in this example.
I also looked at setting up views as we did before in my other problem, but
it seemed like overkill in this problem. I am just trying figure out at
what point I would use this type of solution. What is the clue that tells
you that the best solution is by wrapping one select statement inside of
another select statement.
Thanks,
Tom
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 28 Oct 2005 02:40:20 -0700, tshad wrote:
(snip)
>Haven't tried that yet, but wouldn't the grouping be incorrect as you are
>looking at the value of the column instead of the derived value of that
>expression from the column?
Hi Tom,
Yes, you're right. I forgot that. GROUP BY all columns used in the
expresion makes for a valid query, but it'll only produce the same
results if no two sets of column values can ever result in the same
result of the expression.

>Not really sure why you can't use the assigned title of the column( x as
>column).
This has to do with the "official" way to process a SELECT. Official,
bacuase that's how ANSI says it should be done. In quotes, because all
major databases will choose other orders to optimize for speed, as long
as the results are the same as if the official order had been used.
Step 1: Process FROM clause (includes all JOIN clauses). Results in a
temporary table (stored internally) that holds all columns of all tables
used in the FROM clause, with all rows that satisfy the JOIN conditions.
If old-style FROM cluase is used (i.e. FROM table1, table2, ...), this
will hold the Carthesian product of the tables.
Step 2: Process WHERE clause. Check WHERE clause for each row in temp
table from step 1, and remove row if WHERE clause evaluates to FALSE or
UNKNOWN.
Step 3: Process GROUP BY clause. Using table from step 2, form groups of
rows that share the same value for all columns (or expressions) in the
GROUP BY clause.
Step 4: Process HAVING clause. Check HAVING clause for each *group of
rows* in the temp table after step 3, and remove *complete group* if
HAVING clause evaluates to FALSE or UNKNOWN.
Step 5: Process SELECT clause. Result of this step will be a table with
one column for each entry in the SELECT clause. If no GROUP BY is
present, than result set will have one row for each row left in the temp
table. If a GROUP BY is present, than result set will have one row for
each *group* of rows left in the temp table, and expression in the
SELECT list can't refer to columns/expressions not in the GROUP BY list,
unless enclosed in an aggregate function.
Since the SELECT is processed last, neither the result of the expression
nor the column alias given to it is available when the GROUP BY is
processed.

>That was also what Peter suggested, which worked in this example.
I don't see a message by Peter - had I known that you've already been
given this advise, I wouldn't have repeated it. Was Peter's reply in
this thread?

>I also looked at setting up views as we did before in my other problem, but
>it seemed like overkill in this problem. I am just trying figure out at
>what point I would use this type of solution. What is the clue that tells
>you that the best solution is by wrapping one select statement inside of
>another select statement.
In situations like this, I use a derived table if I have to repeat a
complicated expression. If I also might want to use the same expression
in other queries, I might go for a view. If it's this query only, I
prefer a derived table. If the expression is fairly simple, I just
repeat it.
Those are the rules of thumb. Readability and maintainability of code
are very important too, of course. And for the final decision, at least
when it's in code that needs to be fast, you'll have to test ... test
... test.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hc25m1tmpkh1tdrr17n3oi2fp0e0btpqb5@.
4ax.com...
> On Fri, 28 Oct 2005 02:40:20 -0700, tshad wrote:
> (snip)
> Hi Tom,
> Yes, you're right. I forgot that. GROUP BY all columns used in the
> expresion makes for a valid query, but it'll only produce the same
> results if no two sets of column values can ever result in the same
> result of the expression.
>
> This has to do with the "official" way to process a SELECT. Official,
> bacuase that's how ANSI says it should be done. In quotes, because all
> major databases will choose other orders to optimize for speed, as long
> as the results are the same as if the official order had been used.
> Step 1: Process FROM clause (includes all JOIN clauses). Results in a
> temporary table (stored internally) that holds all columns of all tables
> used in the FROM clause, with all rows that satisfy the JOIN conditions.
> If old-style FROM cluase is used (i.e. FROM table1, table2, ...), this
> will hold the Carthesian product of the tables.
> Step 2: Process WHERE clause. Check WHERE clause for each row in temp
> table from step 1, and remove row if WHERE clause evaluates to FALSE or
> UNKNOWN.
> Step 3: Process GROUP BY clause. Using table from step 2, form groups of
> rows that share the same value for all columns (or expressions) in the
> GROUP BY clause.
> Step 4: Process HAVING clause. Check HAVING clause for each *group of
> rows* in the temp table after step 3, and remove *complete group* if
> HAVING clause evaluates to FALSE or UNKNOWN.
> Step 5: Process SELECT clause. Result of this step will be a table with
> one column for each entry in the SELECT clause. If no GROUP BY is
> present, than result set will have one row for each row left in the temp
> table. If a GROUP BY is present, than result set will have one row for
> each *group* of rows left in the temp table, and expression in the
> SELECT list can't refer to columns/expressions not in the GROUP BY list,
> unless enclosed in an aggregate function.
> Since the SELECT is processed last, neither the result of the expression
> nor the column alias given to it is available when the GROUP BY is
> processed.
>
That makes sense.

>
I assume the derived table is the inner select?
> I don't see a message by Peter - had I known that you've already been
> given this advise, I wouldn't have repeated it. Was Peter's reply in
> this thread?
>
No, it was in the next one - which was a similar question. His result was:
SELECT productname, SUM(balance) AS BALANCE, SUM(days30) AS [30],
SUM(days60) AS [60], SUM(days90) AS [90]
FROM (select ProductName,
Balance = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID)),
Days30 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 0) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 30))),
Days60 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 30) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 60))),
Days90 = (select isnull(sum(PostingsLeft),0)
from Purchasedproducts p2
where (ProductTypeID = 1) and (p1.PurchasedProductID =
p2.PurchasedProductID) and
((DATEDIFF(DAY,GetDate(),DateExpires) > 60) and
(DATEDIFF(DAY,GetDate(),DateExpires) <= 90)))
from purchasedproducts p1 where (ProductTypeID = 1)) AS a
GROUP BY productname
I have no problem seeing another similar answer as it helps to see what is
actually happening to see it from a couple of different angles, even if the
result is the same.

> In situations like this, I use a derived table if I have to repeat a
> complicated expression. If I also might want to use the same expression
> in other queries, I might go for a view. If it's this query only, I
> prefer a derived table. If the expression is fairly simple, I just
> repeat it.
Where it was difficult is trying to figure out that I need to create a
temporary table (derived - I assume) and then do a select on that.
Also, why do you need the "AS Der" (in your example)?
It's not used anywhere. I know that Peter did the same thing with his (AS
a)
Thanks,
Tom
> Those are the rules of thumb. Readability and maintainability of code
> are very important too, of course. And for the final decision, at least
> when it's in code that needs to be fast, you'll have to test ... test
> ... test.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Mon, 31 Oct 2005 16:23:35 -0800, tshad wrote:
(snip)
>I assume the derived table is the inner select?
Yes.

>No, it was in the next one - which was a similar question. His result was
:
>
(snip query)
That query doesn't look too efficient. I know you didn't ask about this
one, but is there any reason why you can't rewrite it as:
SELECT ProductName,
SUM(PostingsLeft) AS Balance,
SUM(CASE WHEN Age > 0 AND Age <= 30
THEN PostingsLeft ELSE 0 END), 0) AS [30],
SUM(CASE WHEN Age > 30 AND Age <= 60
THEN PostingsLeft ELSE 0 END), 0) AS [60],
SUM(CASE WHEN Age > 60 AND Age <= 90
THEN PostingsLeft ELSE 0 END), 0) AS [90]
FROM (SELECT ProductName,
DATEDIFF(day, CURRENT_TIMESTAMP, DateExpires) AS Age
FROM PurchasedProducts) AS a
GROUP BY ProductName
(snip)
>Also, why do you need the "AS Der" (in your example)?
>It's not used anywhere. I know that Peter did the same thing with his (AS
>a)
The syntax requires it. Each column used in a query must be addressable
by tablename-or-alias + columname. Though it is permitted to leave out
the tablename (or alias) in the actual references, it must still be
known to the database engine.
Since a derived table has no own table name, it can only be referenced
through an alias. That's why the syntax REQUIRES you to use an alias
after each derived table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Group Case Sensitive

It appears that table grouping is case sensitive (for example, Re-Roof versus Re-roof appears to be causing a group break). I can't find a parameter to change this behaviour in Reporting Services.
Can anyone verify that it is in fact case sensitive? How to change?

I am running SQL Server 2000 and the database that I am querying is not case sensitive.

In report designer, edit the dataset properties, go to the "Data Options" tab and for the Case Sensitivity property select "False" instead of "Auto".

-- Robert

|||Made the suggested change and it didn't affect the report grouping.
After a little experiment, I can say the group break is definitely case sensitive.|||

Note: you can also just change the grouping expression to make it case-insensitive by applying the LCase() function which converts the string to lower case:

=LCase(Fields!Group.Value)

-- Robert

|||Just now saw the LCase response and tested it on the problem report. It did solve the problem!|||

Just a clarification on what I have found in this case.

Since DataRegion/DataSet/Grouing can be used for scoping puproses I thought it would be good to broaden this case sensitivity to include all of these.

What I found was the following on how RS compares names (short form 'I' for Case Insensitive and 'S' for Case Sensitive):

a) Grouping - S ("a" is different then "A")

b) DataSet - I

c) DataRegion - I

d) Grouping to DataSet - S (Grouping "A" can exist even if DataSet "a" exists)

e) Grouping to DataRegion - I

f) DataSet to DataRegion - I

I am perplexed as to why situations a) and especially d) exist. But it appears to be the way things work.

DK

|||Dataset fields are also S. It's probably because the report definition is compiled to a .NET assembly but I am all for (I).|||

Teo is regarding case-sensitiveness. The main reason is how the ReportObjectModel works and that making scope names case-insensitive would have a negative overall performance impact.

-- Robert

Group Case Sensitive

It appears that table grouping is case sensitive (for example, Re-Roof versus Re-roof appears to be causing a group break). I can't find a parameter to change this behaviour in Reporting Services.
Can anyone verify that it is in fact case sensitive? How to change?

I am running SQL Server 2000 and the database that I am querying is not case sensitive.

In report designer, edit the dataset properties, go to the "Data Options" tab and for the Case Sensitivity property select "False" instead of "Auto".

-- Robert

|||Made the suggested change and it didn't affect the report grouping.
After a little experiment, I can say the group break is definitely case sensitive.|||

Note: you can also just change the grouping expression to make it case-insensitive by applying the LCase() function which converts the string to lower case:

=LCase(Fields!Group.Value)

-- Robert

|||Just now saw the LCase response and tested it on the problem report. It did solve the problem!|||

Just a clarification on what I have found in this case.

Since DataRegion/DataSet/Grouing can be used for scoping puproses I thought it would be good to broaden this case sensitivity to include all of these.

What I found was the following on how RS compares names (short form 'I' for Case Insensitive and 'S' for Case Sensitive):

a) Grouping - S ("a" is different then "A")

b) DataSet - I

c) DataRegion - I

d) Grouping to DataSet - S (Grouping "A" can exist even if DataSet "a" exists)

e) Grouping to DataRegion - I

f) DataSet to DataRegion - I

I am perplexed as to why situations a) and especially d) exist. But it appears to be the way things work.

DK

|||Dataset fields are also S. It's probably because the report definition is compiled to a .NET assembly but I am all for (I).|||

Teo is regarding case-sensitiveness. The main reason is how the ReportObjectModel works and that making scope names case-insensitive would have a negative overall performance impact.

-- Robert

Group Case Sensitive

It appears that table grouping is case sensitive (for example, Re-Roof versus Re-roof appears to be causing a group break). I can't find a parameter to change this behaviour in Reporting Services.
Can anyone verify that it is in fact case sensitive? How to change?

I am running SQL Server 2000 and the database that I am querying is not case sensitive.

In report designer, edit the dataset properties, go to the "Data Options" tab and for the Case Sensitivity property select "False" instead of "Auto".

-- Robert

|||Made the suggested change and it didn't affect the report grouping.
After a little experiment, I can say the group break is definitely case sensitive.
|||

Note: you can also just change the grouping expression to make it case-insensitive by applying the LCase() function which converts the string to lower case:

=LCase(Fields!Group.Value)

-- Robert

|||Just now saw the LCase response and tested it on the problem report. It did solve the problem!|||

Just a clarification on what I have found in this case.

Since DataRegion/DataSet/Grouing can be used for scoping puproses I thought it would be good to broaden this case sensitivity to include all of these.

What I found was the following on how RS compares names (short form 'I' for Case Insensitive and 'S' for Case Sensitive):

a) Grouping - S ("a" is different then "A")

b) DataSet - I

c) DataRegion - I

d) Grouping to DataSet - S (Grouping "A" can exist even if DataSet "a" exists)

e) Grouping to DataRegion - I

f) DataSet to DataRegion - I

I am perplexed as to why situations a) and especially d) exist. But it appears to be the way things work.

DK

|||Dataset fields are also S. It's probably because the report definition is compiled to a .NET assembly but I am all for (I).|||

Teo is regarding case-sensitiveness. The main reason is how the ReportObjectModel works and that making scope names case-insensitive would have a negative overall performance impact.

-- Robert

Group by Month

Pardon my persistant newbieness:
I have something like this:
SELECT
SUM(CASE WHEN ogroup = 'Group_Name' THEN 1 ELSE 0 END) AS 'Open',
SUM(CASE WHEN ogroup = 'Group_Name' AND cgroup = 'Group_Name' THEN 1
ELSE 0 END) AS 'Closed'
FROM
tablename
WHERE
customer = 'customer_name'
and date_value >= '01/01/2005' and date_value < '01/01/2006'
Now I want to group by month and show the month in "mmmm yyyy" format.
Any help?
todSee DATENAME function in SQL Server Books Online. Depending on your client
side display, it might also be better to change the format on the front
end/report.
Anith|||group by year(date_value), month(date_value)
William Stacey [MVP]
"Tod" <todtown@.swbell.net> wrote in message
news:1138311375.334942.46790@.o13g2000cwo.googlegroups.com...
| Pardon my persistant newbieness:
|
| I have something like this:
|
| SELECT
| SUM(CASE WHEN ogroup = 'Group_Name' THEN 1 ELSE 0 END) AS 'Open',
| SUM(CASE WHEN ogroup = 'Group_Name' AND cgroup = 'Group_Name' THEN 1
| ELSE 0 END) AS 'Closed'
| FROM
| tablename
| WHERE
| customer = 'customer_name'
| and date_value >= '01/01/2005' and date_value < '01/01/2006'
|
| Now I want to group by month and show the month in "mmmm yyyy" format.
|
| Any help?
|
| tod
||||Try
SELECT DATENAME(mm, DATEADD(mm, DATEDIFF(mm, 0, date_value), 0) ) +
' ' + DATENAME(yyyy, DATEADD(mm, DATEDIFF(mm, 0, date_value), 0) ),
SUM(CASE WHEN ogroup = 'Group_Name' THEN 1 ELSE 0 END) AS 'Open',
SUM(CASE WHEN ogroup = 'Group_Name' AND cgroup = 'Group_Name' THEN 1
ELSE 0 END) AS 'Closed'
FROM
tablename
WHERE
customer = 'customer_name'
and date_value >= '01/01/2005' and date_value < '01/01/2006'
GROUP BY
DATENAME(mm, DATEADD(mm, DATEDIFF(mm, 0, date_value), 0) ) +
' ' + DATENAME(yyyy, DATEADD(mm, DATEDIFF(mm, 0, date_value), 0) )
"Tod" wrote:

> Pardon my persistant newbieness:
> I have something like this:
> SELECT
> SUM(CASE WHEN ogroup = 'Group_Name' THEN 1 ELSE 0 END) AS 'Open',
> SUM(CASE WHEN ogroup = 'Group_Name' AND cgroup = 'Group_Name' THEN 1
> ELSE 0 END) AS 'Closed'
> FROM
> tablename
> WHERE
> customer = 'customer_name'
> and date_value >= '01/01/2005' and date_value < '01/01/2006'
> Now I want to group by month and show the month in "mmmm yyyy" format.
> Any help?
> tod
>|||On 26 Jan 2006 13:36:15 -0800, Tod wrote:

>Pardon my persistant newbieness:
>I have something like this:
>SELECT
> SUM(CASE WHEN ogroup = 'Group_Name' THEN 1 ELSE 0 END) AS 'Open',
> SUM(CASE WHEN ogroup = 'Group_Name' AND cgroup = 'Group_Name' THEN 1
>ELSE 0 END) AS 'Closed'
>FROM
> tablename
>WHERE
> customer = 'customer_name'
> and date_value >= '01/01/2005' and date_value < '01/01/2006'
>Now I want to group by month and show the month in "mmmm yyyy" format.
>Any help?
>tod
Hi Tod,
SELECT
DATEADD(month, DATEDIFF(month, 0, date_value), 0) AS Month,
SUM(CASE WHEN ogroup = 'Group_Name' THEN 1 ELSE 0 END) AS
'Open',
SUM(CASE WHEN ogroup = 'Group_Name' AND cgroup = 'Group_Name'
THEN 1 ELSE 0 END) AS 'Closed'
FROM
tablename
WHERE
customer = 'customer_name'
and date_value >= '20050101' and date_value < '20060101'
GROUP BY
DATEDIFF(month, 0, date_value)
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||oops. Try this and add your other stuff.
select DATENAME(mm, min(Date)) + ' ' +datename(yyyy, min(Date))
from @.t
group by year(Date), month(Date)
William Stacey [MVP]
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:OPHi1QsIGHA.3936@.TK2MSFTNGP12.phx.gbl...
| group by year(date_value), month(date_value)
|
|
| --
| William Stacey [MVP]
|
| "Tod" <todtown@.swbell.net> wrote in message
| news:1138311375.334942.46790@.o13g2000cwo.googlegroups.com...
|| Pardon my persistant newbieness:
||
|| I have something like this:
||
|| SELECT
|| SUM(CASE WHEN ogroup = 'Group_Name' THEN 1 ELSE 0 END) AS 'Open',
|| SUM(CASE WHEN ogroup = 'Group_Name' AND cgroup = 'Group_Name' THEN 1
|| ELSE 0 END) AS 'Closed'
|| FROM
|| tablename
|| WHERE
|| customer = 'customer_name'
|| and date_value >= '01/01/2005' and date_value < '01/01/2006'
||
|| Now I want to group by month and show the month in "mmmm yyyy" format.
||
|| Any help?
||
|| tod
||
|
|

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'

Group by and Case Statements

Hi,
I have a query where I am using a case statement and a group by e.g.
Select Field1, Field2, Field3, Max(Field4),
CASE
WHEN <something> THEN ' '
WHEN <something> THEN 1
END as Field4
WHERE <something>
GROUP BY Field1, Field2, Field3, Field4
The problem I am having is the group by isn't using the value from
the Case Statement, it's not grouping the records correctly. Anyone
have any idea's why that would happen?
Thanks for the helpBy definition, the expressions in the GROUP BY clause reffer to the
columns from the table, not to the columns defined in the SELECT
clause. You probably need to use a subquery for this:
SELECT Field1, Field2, Field3, MAX(Field4), TheExpression as Field4
FROM (
SELECT Field1, Field2, Field3, Field4,
CASE
WHEN <something> THEN ' '
WHEN <something> THEN 1
END as TheExpression
FROM <your table>
WHERE <something>
) x
GROUP BY Field1, Field2, Field3, TheExpression
Razvan|||Thanks, that's what I figured. I was hoping there was a more eligant
way around that.
Thanks|||Simply copy the same CASE expression used in the SELECT list into the
GROUP BY:
Select Field1, Field2, Field3, Max(Field4),
CASE
WHEN <something> THEN ' '
WHEN <something> THEN 1
END as Field4
WHERE <something>
GROUP BY Field1, Field2, Field3,
CASE
WHEN <something> THEN ' '
WHEN <something> THEN 1
END
Roy Harvey
Beacon Falls, CT
On 3 May 2006 09:25:44 -0700, "Red2" <sdibello@.gmail.com> wrote:

>Hi,
> I have a query where I am using a case statement and a group by e.g.
> Select Field1, Field2, Field3, Max(Field4),
> CASE
> WHEN <something> THEN ' '
> WHEN <something> THEN 1
> END as Field4
> WHERE <something>
> GROUP BY Field1, Field2, Field3, Field4
> The problem I am having is the group by isn't using the value from
>the Case Statement, it's not grouping the records correctly. Anyone
>have any idea's why that would happen?
>Thanks for the help|||Red2 wrote:
> Hi,
> I have a query where I am using a case statement and a group by e.g.
> Select Field1, Field2, Field3, Max(Field4),
> CASE
> WHEN <something> THEN ' '
> WHEN <something> THEN 1
> END as Field4
> WHERE <something>
> GROUP BY Field1, Field2, Field3, Field4
> The problem I am having is the group by isn't using the value from
> the Case Statement, it's not grouping the records correctly. Anyone
> have any idea's why that would happen?
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
It'd be better if you showed the actual query. Your example has a
Max(Field4) and the CASE expression is labeled as Field4. If the GROUP
BY clause has Field4 is will be grouping the Field4 in the Max()
function. Obviously, not what you want to do. Is this a misnomer or
are you really using the same column in the Max() function and as the
CASE expression's name?
If you want to GROUP BY the result of a CASE expression you have to
include the CASE expression in the GROUP BY clause:
GROUP BY field1, field2, field3, CASE WHEN <something> THEN X ELSE Y END
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBRFkUgYechKqOuFEgEQLZ2ACdHDbb0StLG/MEkaOpME4KfeDYTfcAn1I2
EEZOhQ/brevVe0gSJNujPhER
=aD2k
--END PGP SIGNATURE--

GridView wont delete or update

I have had this problem before but it turned out to be dodgy SQL created by the wizard. Doesn't seem to be the case this time.

The following does a postback but makes no changes.

1<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ehlConnectionString %>"2DeleteCommand="DELETE FROM [tblSubRegions] WHERE [SubRegionID] = ?"3InsertCommand="INSERT INTO [tblSubRegions] ([SubRegionID], [RegionID], [SubRegionName]) VALUES (?, ?, ?)"4ProviderName="<%$ ConnectionStrings:ehlConnectionString.ProviderName %>"5SelectCommand="SELECT tblSubRegions.SubRegionID, tblSubRegions.RegionID, tblSubRegions.SubRegionName, tblRegions.RegionName FROM (tblSubRegions INNER JOIN tblRegions ON tblSubRegions.RegionID = tblRegions.RegionID) WHERE (tblSubRegions.RegionID = ?) ORDER BY tblSubRegions.SubRegionName"6UpdateCommand="UPDATE [tblSubRegions] SET [RegionID] = ?, [SubRegionName] = ? WHERE [SubRegionID] = ?">78<DeleteParameters>9 <asp:Parameter Name="SubRegionID" Type="Int32" />10</DeleteParameters>1112<UpdateParameters>13<asp:Parameter Name="RegionID" Type="Int32" />14<asp:Parameter Name="SubRegionName" Type="String" />15<asp:Parameter Name="SubRegionID" Type="Int32" />16</UpdateParameters>1718<SelectParameters>19<asp:ControlParameter ControlID="dropRegions" Name="RegionID" PropertyName="SelectedValue" Type="Int32" />20</SelectParameters>2122<InsertParameters>23<asp:Parameter Name="SubRegionID" Type="Int32" />24<asp:Parameter Name="RegionID" Type="Int32" />25<asp:Parameter Name="SubRegionName" Type="String" />26</InsertParameters>2728</asp:SqlDataSource>29303132<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ehlConnectionString %>"33ProviderName="<%$ ConnectionStrings:ehlConnectionString.ProviderName %>"34SelectCommand="SELECT [RegionID], [RegionName] FROM [tblRegions]">3536</asp:SqlDataSource>37383940<asp:DropDownList id="dropStates" runat="server" OnSelectedIndexChanged="dropStates_SelectedIndexChanged" AutoPostBack="True">41</asp:DropDownList>4243<asp:DropDownList id="dropRegions" runat="server" OnSelectedIndexChanged="dropRegions_SelectedIndexChanged" AutoPostBack="True">44</asp:DropDownList>45464748 <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"49 AutoGenerateColumns="False" EnableViewState=false Width="100%" DataSourceID="SqlDataSource1">50 <Columns>51 <asp:TemplateField HeaderText="SubRegionName" SortExpression="SubRegionName">52 <EditItemTemplate>53 <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"54 DataTextField="RegionName" DataValueField="RegionID" SelectedValue='<%# Bind("RegionID") %>'>55 </asp:DropDownList>56 </EditItemTemplate>57 <ItemTemplate>58 <asp:Label ID="Label1" runat="server" Text='<%# Bind("SubRegionName") %>'></asp:Label>59 </ItemTemplate>60 </asp:TemplateField>61 <asp:BoundField DataField="RegionName" HeaderText="RegionName" SortExpression="RegionName" />62 <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />63 </Columns>64 </asp:GridView>

Thanks in advance.

Shaun

You need to set DataKeyNames="yourprimarykey"