GROUPING problem

I am trying to get counts of jobs accum into 4 columns by date. When I try
to save the view it gives me the error "Column 'dbo.RepairOrder.JobSize' is
invalid in the select list because it is not contained in either an
aggregate function or the GROUP BY clause."
I don't want to group by JobSize. Below is my code if someone can help me
resolve this. Thanks.
David
ALTER VIEW dbo.vw_JobSizeByDate
AS
SELECT ScheduledInDate,
XsmallJobs = CASE
WHEN JobSize = 'X' THEN 1
ELSE 0
END,
SmallJobs = CASE
WHEN JobSize = 'S' THEN 1
ELSE 0
END,
MedJobs = CASE
WHEN JobSize = 'M' THEN 1
ELSE 0
END,
HeavyJobs = CASE
WHEN JobSize = 'H' THEN 1
ELSE 0
END
FROM dbo.RepairOrder
WHERE (RepairOrderID IS NOT NULL)
GROUP BY ScheduledInDate
HAVING (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))All that is missing is the SUM() - the missing aggregate function of
the error message - around each CASE expression:
XsmallJobs = SUM(CASE
WHEN JobSize = 'X' THEN 1
ELSE 0
END),
Roy Harvey
Beacon Falls, CT
On Fri, 21 Apr 2006 17:03:36 -0500, "David" <dlchase@.lifetimeinc.com>
wrote:

>I am trying to get counts of jobs accum into 4 columns by date. When I try
>to save the view it gives me the error "Column 'dbo.RepairOrder.JobSize' is
>invalid in the select list because it is not contained in either an
>aggregate function or the GROUP BY clause."
>I don't want to group by JobSize. Below is my code if someone can help me
>resolve this. Thanks.
>David
>ALTER VIEW dbo.vw_JobSizeByDate
>AS
>SELECT ScheduledInDate,
>XsmallJobs = CASE
>WHEN JobSize = 'X' THEN 1
>ELSE 0
>END,
>SmallJobs = CASE
>WHEN JobSize = 'S' THEN 1
>ELSE 0
>END,
>MedJobs = CASE
>WHEN JobSize = 'M' THEN 1
>ELSE 0
>END,
>HeavyJobs = CASE
>WHEN JobSize = 'H' THEN 1
>ELSE 0
>END
>FROM dbo.RepairOrder
>WHERE (RepairOrderID IS NOT NULL)
>GROUP BY ScheduledInDate
>HAVING (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))
>|||why are you grouping at all, maybe I've missed it, but I don't see any
aggregate functions, just add the having clause as an AND to the where,
and remove the group by:
FROM dbo.RepairOrder
WHERE (RepairOrderID IS NOT NULL)
AND (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))|||Use the CASE expressions inside aggregate functions:
ALTER VIEW dbo.vw_JobSizeByDate
AS
SELECT ScheduledInDate,
SUM(CASE WHEN JobSize = 'X' THEN 1 ELSE 0 END) AS "XsmallJobs",
SUM(CASE WHEN JobSize = 'S' THEN 1 ELSE 0 END) AS "SmallJobs",
SUM(CASE WHEN JobSize = 'M' THEN 1 ELSE 0 END) AS "MedJobs",
SUM(CASE WHEN JobSize = 'H' THEN 1 ELSE 0 END) AS "HeavyJobs"
FROM dbo.RepairOrder
WHERE (RepairOrderID IS NOT NULL)
GROUP BY ScheduledInDate
HAVING (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))
"David" wrote:

> I am trying to get counts of jobs accum into 4 columns by date. When I tr
y
> to save the view it gives me the error "Column 'dbo.RepairOrder.JobSize' i
s
> invalid in the select list because it is not contained in either an
> aggregate function or the GROUP BY clause."
> I don't want to group by JobSize. Below is my code if someone can help me
> resolve this. Thanks.
> David
> ALTER VIEW dbo.vw_JobSizeByDate
> AS
> SELECT ScheduledInDate,
> XsmallJobs = CASE
> WHEN JobSize = 'X' THEN 1
> ELSE 0
> END,
> SmallJobs = CASE
> WHEN JobSize = 'S' THEN 1
> ELSE 0
> END,
> MedJobs = CASE
> WHEN JobSize = 'M' THEN 1
> ELSE 0
> END,
> HeavyJobs = CASE
> WHEN JobSize = 'H' THEN 1
> ELSE 0
> END
> FROM dbo.RepairOrder
> WHERE (RepairOrderID IS NOT NULL)
> GROUP BY ScheduledInDate
> HAVING (ScheduledInDate > CONVERT(DATETIME, '2005-12-31 00:00:00', 102))
>
>|||Someone actually put a "vw-"prefix on your view name! They did not
know the ISO-11179 standards - unless this table deals with
Volkswagens. Also, use the portable AS syntax instead of dialect =.
Can I assume that you have more than one repair order, in spite of a
singular table name?
Your WHERE and HAVING clauses made no sense. How can a
"repair_order_id" ever be NULL? What is the definition of an
identifier? Why are you casting temporal data to strings? That would
imply your DDL is soooo screwed up that temporal data is in strings!
Try this, after you clean up the DDL.
CREATE VIEW JobsizeByDate
(xsmalljob_cnt,
smalljob_cnt,
medjob_cnt,
heavyjob_cnt)
AS
SELECT scheduledin_date,
SUM(CASE WHEN jobsize = 'x' THEN 1 ELSE 0 END),
SUM(CASE WHEN jobsize = 's' THEN 1 ELSE 0 END),
SUM(CASE WHEN jobsize = 'm' THEN 1 ELSE 0 END),
SUM(CASE WHEN jobsize = 'h' THEN 1 ELSE 0 END)
FROM RepairOrders
GROUP BY scheduledin_date;|||Perfect. That worked. Thanks.
David
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:fimi42l7diferi1jmlaa6k1anf8lvo6t1d@.
4ax.com...
> All that is missing is the SUM() - the missing aggregate function of
> the error message - around each CASE expression:
> XsmallJobs = SUM(CASE
> WHEN JobSize = 'X' THEN 1
> ELSE 0
> END),
> Roy Harvey
> Beacon Falls, CT
>
> On Fri, 21 Apr 2006 17:03:36 -0500, "David" <dlchase@.lifetimeinc.com>
> wrote:
>|||Ah the beauty of an sql dbms...the overblown importance of columns names :P
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145658584.662773.174100@.i39g2000cwa.googlegroups.com...
> Someone actually put a "vw-"prefix on your view name! They did not
> know the ISO-11179 standards - unless this table deals with
> Volkswagens. Also, use the portable AS syntax instead of dialect =.
> Can I assume that you have more than one repair order, in spite of a
> singular table name?
> Your WHERE and HAVING clauses made no sense. How can a
> "repair_order_id" ever be NULL? What is the definition of an
> identifier? Why are you casting temporal data to strings? That would
> imply your DDL is soooo screwed up that temporal data is in strings!
> Try this, after you clean up the DDL.
> CREATE VIEW JobsizeByDate
> (xsmalljob_cnt,
> smalljob_cnt,
> medjob_cnt,
> heavyjob_cnt)
> AS
> SELECT scheduledin_date,
> SUM(CASE WHEN jobsize = 'x' THEN 1 ELSE 0 END),
> SUM(CASE WHEN jobsize = 's' THEN 1 ELSE 0 END),
> SUM(CASE WHEN jobsize = 'm' THEN 1 ELSE 0 END),
> SUM(CASE WHEN jobsize = 'h' THEN 1 ELSE 0 END)
> FROM RepairOrders
> GROUP BY scheduledin_date;
>|||Why do you think the vague, non-standard names will make for a good
database? That they will port? That a data dictionary will appear
magically from them? That ISO is a waste of time? That 30+ years of
SE research is wrong?|||There is a certain quality to your quantity of orthodoxy.
But you have missed the mark:)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145668737.205833.119960@.j33g2000cwa.googlegroups.com...
> Why do you think the vague, non-standard names will make for a good
> database? That they will port? That a data dictionary will appear
> magically from them? That ISO is a waste of time? That 30+ years of
> SE research is wrong?
>

Grouping and counting

Hi,
I will try to explain:
I want the proper grouping and display counts. This query
works fine, see below some of the returned results.
SELECT TempD.state, TempD.City, TempD.Zip, count(TempE.ID)AS total
FROM
TempE INNER JOIN TempD
ON TempE.Numb = TempD.Numb
WHERE(TempE.sp in('bm','pm') or
TempE.sp2 in('bm','pm'))
GROUP BY TempD.state,TempD.City,TempD.Zip
ORDER BY TempD.state, TempD.City
Some of the results returned:
State City Zip total
Alabama ALBASTER 35007 2
Alabama BIRMINGHAM 35292 1
Arizona YUMA 85364 1
California PALMDALE 93550 1
Connecticut NEW LONDON 06320 2
Connecticut WOODBRIDGE 06525 1
Delaware CLAYMONT 19703 1
Delaware WILMINGTON 19801 3
North Carolina ASHEVILLE 28815 10
North Carolina ASHEVILLE 28816 3
South Carolina CHAPIN 29036 1
South Carolina CHARLESTON 29401 116
South Carolina CHARLESTON 29402 8
I want two more columns after total for "bm" and "pm" and with
the totals broke down like the following
State City Zip total
bm pm
Alabama ALBASTER 35007 2 1
1
Alabama BIRMINGHAM 35292 1 1 0
Arizona YUMA 85364 1 0
1
California PALMDALE 93550 1 1
0
Connecticut NEW LONDON 06320 2 2 0
Connecticut WOODBRIDGE 06525 1 0 1
Delaware CLAYMONT 19703 1 1 0
Delaware WILMINGTON 19801 3 3 0
North Carolina ASHEVILLE 28815 10 7 3
North Carolina ASHEVILLE 28816 3 0 3
South Carolina CHAPIN 29036 1 1
0
South Carolina CHARLESTON 29401 116 86 30
South Carolina CHARLESTON 29402 8 2 6
hope I was clear, Thanks
gvTry using a "case" expression.
SELECT
TempD.state,
TempD.City,
TempD.Zip,
count(TempE.ID)AS total,
sum(case when TempE.sp = 'bm' or TempE.sp2 = 'bm' then 1 else 0 end) as bm,
sum(case when TempE.sp = 'pm' or TempE.sp2 = 'pm' then 1 else 0 end) as pm
from
..
AMB
"gv" wrote:

> Hi,
> I will try to explain:
> I want the proper grouping and display counts. This query
> works fine, see below some of the returned results.
> SELECT TempD.state, TempD.City, TempD.Zip, count(TempE.ID)AS total
> FROM
> TempE INNER JOIN TempD
> ON TempE.Numb = TempD.Numb
> WHERE(TempE.sp in('bm','pm') or
> TempE.sp2 in('bm','pm'))
> GROUP BY TempD.state,TempD.City,TempD.Zip
> ORDER BY TempD.state, TempD.City
> Some of the results returned:
> State City Zip total
> Alabama ALBASTER 35007 2
> Alabama BIRMINGHAM 35292 1
> Arizona YUMA 85364 1
> California PALMDALE 93550 1
> Connecticut NEW LONDON 06320 2
> Connecticut WOODBRIDGE 06525 1
> Delaware CLAYMONT 19703 1
> Delaware WILMINGTON 19801 3
> North Carolina ASHEVILLE 28815 10
> North Carolina ASHEVILLE 28816 3
> South Carolina CHAPIN 29036 1
> South Carolina CHARLESTON 29401 116
> South Carolina CHARLESTON 29402 8
> I want two more columns after total for "bm" and "pm" and with
> the totals broke down like the following
> State City Zip total
> bm pm
> Alabama ALBASTER 35007 2 1
> 1
> Alabama BIRMINGHAM 35292 1 1 0
> Arizona YUMA 85364 1
0
> 1
> California PALMDALE 93550 1 1
> 0
> Connecticut NEW LONDON 06320 2 2 0
> Connecticut WOODBRIDGE 06525 1 0 1
> Delaware CLAYMONT 19703 1 1
0
> Delaware WILMINGTON 19801 3 3 0
> North Carolina ASHEVILLE 28815 10 7 3
> North Carolina ASHEVILLE 28816 3 0
3
> South Carolina CHAPIN 29036 1 1
> 0
> South Carolina CHARLESTON 29401 116 86 30
> South Carolina CHARLESTON 29402 8 2 6
> hope I was clear, Thanks
> gv
>
>
>|||Thanks!!
gv
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:1996E862-0C8C-4C22-A154-57D5B728A650@.microsoft.com...
> Try using a "case" expression.
> SELECT
> TempD.state,
> TempD.City,
> TempD.Zip,
> count(TempE.ID)AS total,
> sum(case when TempE.sp = 'bm' or TempE.sp2 = 'bm' then 1 else 0 end) as
> bm,
> sum(case when TempE.sp = 'pm' or TempE.sp2 = 'pm' then 1 else 0 end) as
> pm
> from
> ...
>
> AMB
> "gv" wrote:
>

Group By Issue

I was asked to display two fields plus a total counts of the table but only need to group by one field and leave the other field as Display field. For example:

Select A, B, Count(*)
From Table
where ...
Group By B

It won't work on SQL unless you group both fields. Any way to get around this?

Thanks!

J827Think about it, and you'll see that it makes no logical sense.

If value A remains constant for any value B, then go ahead and group by A, as it will not affect your output.

Select A, B, Count(*)
From Table
where ...
Group By A, B

If value A varies for any given value B, then which value are you going to show in your output? You need some type of criteria for deciding. You could, for instance, use the lowest value of A:

Select min(A) as A, B, Count(*)
From Table
where ...
Group By B

I think you need to better define, or at least better explain, what your objective is.|||blindman,

Thank you for your quick response!

The value A actually is coming from different table and it is not a constant for any value B. If not grouping B, they are just part of combination running results based on the business logic but my business partner would like to display both fields in the report for no calculations should be run off of A field. Is this feasible or not?

J827|||Calculate your B totals in a subquery:

select TableA.A, Subquery.B, Subquery.RecordCount
from TableA
left outer join (select B, count(*) as RecordCount from TableB group by B) Subquery
on TableA.B = Subquery.B|||GROUP BY can return one row for each column you specify in the GROUP BY clause, plus any additional aggregates of that group as a column.

So if you have TableA, with columns Title, Sales, Price, a valid GROUP BY would be:

SELECT Title, SUM(Sales) AS Sales, MAX(PRICE) AS MaxPrice, (SUM(SALES) * (SUM(PRICE)) AS Total FROM TableA GROUP BY Title

If the values for ValueB are constant with respect to a specfic value of ValueA, then you can kinda fudge this by using a MIN or MAX function. In which case you don't need to include the column in the GROUP BY clause because MIN and MAX are aggregate functions.

Group breaking prematurely

I have a report that simply shows counts, amount and an extended amount
grouped by 1) telephone exchange (exchange) then 2) tariff's being billed
(st_s_usoc). The problem I am having is the tariff group is prematurely
breaking within the group as shown by the example below. I also included the
sql code behind the report further below. Does anyone have any suggestions
for me as to what to look at that could be possibly causing this break. I
created the same exact report in Crystal Reports and the report is grouping
correctly.
Exch USOC Description Quantity Amount Ext
Amount
258
400R Call Forwarding 2 2.65
5.30
401R Busy Call Forward 2 2.65
5.30
404 Call Waiting 1
6.65 6.65
404 Call Waiting 4
6.65 26.60
(there should be one line for the 404 USOC)
SELECT st_s_usoc, st_i_quantity, tm_s_desc_1, tm_m_amount, exchange,
Extended_Amount
FROM vw_VTC_Sub_Tariff_count
WHERE st_dt_start_date < @.StartDateParm AND st_dt_stop_date IS NULL
ORDER BY exchange, st_s_usoc
Any help would be greatly appreciated.Your post is not very clear . So grouping is done first by exchange
( which is column 1 ? ) and then "extended amount" ( is that column
2 ?) .You have written "there should be one line for the 404 USOC" ,
do you mean that this row has a different exchange and should form a
new group ?Explain the abbreveations a bit, what is USOC ?
Cheers
Shai
On Nov 24, 5:02 am, Wishing I was skiing mom
<WishingIwasskiing...@.discussions.microsoft.com> wrote:
> I have a report that simply shows counts, amount and an extended amount
> grouped by 1) telephone exchange (exchange) then 2) tariff's being billed
> (st_s_usoc). The problem I am having is the tariff group is prematurely
> breaking within the group as shown by the example below. I also included the
> sql code behind the report further below. Does anyone have any suggestions
> for me as to what to look at that could be possibly causing this break. I
> created the same exact report in Crystal Reports and the report is grouping
> correctly.
> Exch USOC Description Quantity Amount Ext
> Amount
> 258
> 400R Call Forwarding 2 2.65
> 5.30
> 401R Busy Call Forward 2 2.65
> 5.30
> 404 Call Waiting 1
> 6.65 6.65
> 404 Call Waiting 4
> 6.65 26.60
> (there should be one line for the 404 USOC)
> SELECT st_s_usoc, st_i_quantity, tm_s_desc_1, tm_m_amount, exchange,
> Extended_Amount
> FROM vw_VTC_Sub_Tariff_count
> WHERE st_dt_start_date < @.StartDateParm AND st_dt_stop_date IS NULL
> ORDER BY exchange, st_s_usoc
> Any help would be greatly appreciated.|||Sorry about that, but yes unfortunately what you see on the post doesn't look
exactly like what I had typed. It wrapped the lines a bit. There should be
four lines one for USOC 400R, 401R and two for 404. The problem is the two
404 lines, this should be combined into one line, I can not figure out what
is causing the report to break into two lines. I'm stuck. USOC(stands for
Universal Service ', basically it's a telephone service tariff)
Thank you for attention regarding this.
Jackie
"shaikat.das@.gmail.com" wrote:
> Your post is not very clear . So grouping is done first by exchange
> ( which is column 1 ? ) and then "extended amount" ( is that column
> 2 ?) .You have written "there should be one line for the 404 USOC" ,
> do you mean that this row has a different exchange and should form a
> new group ?Explain the abbreveations a bit, what is USOC ?
> Cheers
> Shai
>
> On Nov 24, 5:02 am, Wishing I was skiing mom
> <WishingIwasskiing...@.discussions.microsoft.com> wrote:
> > I have a report that simply shows counts, amount and an extended amount
> > grouped by 1) telephone exchange (exchange) then 2) tariff's being billed
> > (st_s_usoc). The problem I am having is the tariff group is prematurely
> > breaking within the group as shown by the example below. I also included the
> > sql code behind the report further below. Does anyone have any suggestions
> > for me as to what to look at that could be possibly causing this break. I
> > created the same exact report in Crystal Reports and the report is grouping
> > correctly.
> >
> > Exch USOC Description Quantity Amount Ext
> > Amount
> > 258
> > 400R Call Forwarding 2 2.65
> > 5.30
> > 401R Busy Call Forward 2 2.65
> > 5.30
> > 404 Call Waiting 1
> > 6.65 6.65
> > 404 Call Waiting 4
> > 6.65 26.60
> >
> > (there should be one line for the 404 USOC)
> >
> > SELECT st_s_usoc, st_i_quantity, tm_s_desc_1, tm_m_amount, exchange,
> > Extended_Amount
> > FROM vw_VTC_Sub_Tariff_count
> > WHERE st_dt_start_date < @.StartDateParm AND st_dt_stop_date IS NULL
> > ORDER BY exchange, st_s_usoc
> >
> > Any help would be greatly appreciated.
>|||On Nov 23, 3:02 pm, Wishing I was skiing mom
<WishingIwasskiing...@.discussions.microsoft.com> wrote:
> I have a report that simply shows counts, amount and an extended amount
> grouped by 1) telephone exchange (exchange) then 2) tariff's being billed
> (st_s_usoc). The problem I am having is the tariff group is prematurely
> breaking within the group as shown by the example below. I also included the
> sql code behind the report further below. Does anyone have any suggestions
> for me as to what to look at that could be possibly causing this break. I
> created the same exact report in Crystal Reports and the report is grouping
> correctly.
> Exch USOC Description Quantity Amount Ext Amount
> 258
> 400R Call Forwarding 2 2.65 5.30
> 401R Busy Call Forward 2 2.65 5.30
> 404 Call Waiting 1 6.65 6.65
> 404 Call Waiting 4 6.65 26.60
> (there should be one line for the 404 USOC)
>
> Any help would be greatly appreciated.
I would check your data in preview filtering on EXCHANGE=404... The
query should return only the 5 rows, then change your SQL to do a
GROUP BY so the server returns the data in the form that you expect.
If you still see two groups with the 404 data, then it's a data
issue.
Make sure that your data fields are not right-padded with spaces, and
that your database converts zero-length strings to NULLs (perhaps the
USOC field, though appearing empty, really isn't). Try playing with
the TRIM command to truncate trailing spaces.
In the Expressions, try an Expression of
= "(" & Fields!ABCXYZ.Value & ")"
to make sure that there aren't any weird characters appended to your
strings.
One thing we ran into recently was Char(191) in a Memo field -- a
"hard space" that in HTML rendered as a space but to a String Compare
(which is all a Matrix grouping is) they are different.
-- Scott|||Thanks Scott for your suggestions,
At least I was able to eliminate the possibility of it being a data issue,
after using your group by suggestion. I am going to forward this issue on to
a R.S. instructor I had and hopefully he knows what might be causing this.
Then perhaps a MSDN incident, who knows may it's a bug.
Thanks again,
Jackie
"Orne" wrote:
> On Nov 23, 3:02 pm, Wishing I was skiing mom
> <WishingIwasskiing...@.discussions.microsoft.com> wrote:
> > I have a report that simply shows counts, amount and an extended amount
> > grouped by 1) telephone exchange (exchange) then 2) tariff's being billed
> > (st_s_usoc). The problem I am having is the tariff group is prematurely
> > breaking within the group as shown by the example below. I also included the
> > sql code behind the report further below. Does anyone have any suggestions
> > for me as to what to look at that could be possibly causing this break. I
> > created the same exact report in Crystal Reports and the report is grouping
> > correctly.
> >
> > Exch USOC Description Quantity Amount Ext Amount
> > 258
> > 400R Call Forwarding 2 2.65 5.30
> > 401R Busy Call Forward 2 2.65 5.30
> > 404 Call Waiting 1 6.65 6.65
> > 404 Call Waiting 4 6.65 26.60
> >
> > (there should be one line for the 404 USOC)
> >
> >
> > Any help would be greatly appreciated.
> I would check your data in preview filtering on EXCHANGE=404... The
> query should return only the 5 rows, then change your SQL to do a
> GROUP BY so the server returns the data in the form that you expect.
> If you still see two groups with the 404 data, then it's a data
> issue.
> Make sure that your data fields are not right-padded with spaces, and
> that your database converts zero-length strings to NULLs (perhaps the
> USOC field, though appearing empty, really isn't). Try playing with
> the TRIM command to truncate trailing spaces.
> In the Expressions, try an Expression of
> = "(" & Fields!ABCXYZ.Value & ")"
> to make sure that there aren't any weird characters appended to your
> strings.
> One thing we ran into recently was Char(191) in a Memo field -- a
> "hard space" that in HTML rendered as a space but to a String Compare
> (which is all a Matrix grouping is) they are different.
> -- Scott
>