Solved

Need to get one more column in the SELECT

Posted on 2012-04-05
44
422 Views
Last Modified: 2012-04-11
select Max(ChartCount)
from
(
select
Month(last_date) as [Mth],
Count(distinct Chart_No) as [ChartCount]
from Treatment
where last_date >= '2011-01-01'
and last_date < '2012-01-01'
Group by Month(last_date)
) a


In above, how do I include Month(last_date) as [Mth]?

Thanks,
Ashok
0
Comment
Question by:Ashok
  • 19
  • 12
  • 6
  • +2
44 Comments
 
LVL 9

Assisted Solution

by:damerval
damerval earned 500 total points
ID: 37812703
Try this:
select Mth, ChartCount
from
(
select
Month(last_date) as [Mth],
Count(distinct Chart_No) as [ChartCount]
from Treatment
where last_date >= '2011-01-01'
and last_date < '2012-01-01'
Group by Month(last_date)
) a
WHERE ChartCount = (
select Max(ChartCount)
from
(
select
Month(last_date) as [Mth],
Count(distinct Chart_No) as [ChartCount]
from Treatment
where last_date >= '2011-01-01'
and last_date < '2012-01-01'
Group by Month(last_date)
) )

HTH

Philippe
0
 
LVL 13

Accepted Solution

by:
Ashok earned 0 total points
ID: 37812734
Philippe,

select Mth, ChartCount
from
(
select
Month(last_date) as [Mth],
Count(distinct Chart_No) as [ChartCount]
from Treatment
where last_date >= '2011-01-01'
and last_date < '2012-01-01'
Group by Month(last_date)
) a
WHERE ChartCount = (
select Max(ChartCount)
from
(
select
Month(last_date) as [Mth],
Count(distinct Chart_No) as [ChartCount]
from Treatment
where last_date >= '2011-01-01'
and last_date < '2012-01-01'
Group by Month(last_date)
) b ) ;

You were missing b in the second select, otherwise your solution is working.

Thanks,
Ashok
0
 
LVL 9

Expert Comment

by:damerval
ID: 37812767
The "b" should not be needed, as no fields from the subquery it refers to are used anywhere else in the query. The statement should work without it. If the table can be provided with obfuscated data I am confident I can resolve whatever issue was making it seem like it was required.
0
 
LVL 13

Author Comment

by:Ashok
ID: 37812793
without b
in SQL Server 2000

I get

Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near ')'.

Thanks,
Ashok
0
 
LVL 13

Author Comment

by:Ashok
ID: 37812797
After adding b, it works.

The error is gone.

Ashok
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37812798
If the table can be provided with obfuscated data I am confident I can resolve whatever issue was making it seem like it was required.
There is no need, 10 seconds testing in SSMS would tell you it does not compile without the alias.  You get the following error message:
Incorrect syntax near ')'.
0
 
LVL 3

Expert Comment

by:Chris__W
ID: 37812799
The "missing b" is entirely unnecessary.  Aliases are not required, nor is he referencing any.
0
 
LVL 13

Author Comment

by:Ashok
ID: 37812802
It could be that SQL Server 2000 is buggy.

and

you may be correct if I was using SQL Server 2005 or 2008.

Ashok
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37812864
Chris__W,
The "missing b" is entirely unnecessary
Please do your homework first.  It is very simple:
1. Copy
2. Paste
3. Execute
0
 
LVL 9

Expert Comment

by:damerval
ID: 37812898
I'm surprised, but happy nonetheless that it is working for you. I don't have Sql Server 2000 to do any testing on so will file that one in my brain under "weird". It will be in good company.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37812909
I don't have Sql Server 2000 to do any testing on so will file that one in my brain under "weird".
That is the way SQL Server 7, 2000, 2005, 2008, 2008-R2 and 2012 operate.  You can test it for yourself using SSMS and I swear it will not take you more than 10 seconds to become a believer.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37812938
Did I mention it is all documented in SQL Server's BOL?  You can see for yourself by going to the FROM clause entry.
0
 
LVL 13

Author Comment

by:Ashok
ID: 37812940
To make it work for both SQL Server 2000 and SQL Server 2008,

why can't we add the "b"?
How long does it take to type one character?

Does it work in SQL Server 2005 or 2008 if you have the unnecessary "b"?

Thanks,
Ashok
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37812947
Here is the relevant section for those that do not like reading BOL:
http://msdn.microsoft.com/en-us/library/ms177634.aspx
[AS] table_alias
Is an alias for table_source that can be used either for convenience or to distinguish a table or view in a self-join or subquery. An alias is frequently a shortened table name used to refer to specific columns of the tables in a join. If the same column name exists in more than one table in the join, SQL Server requires that the column name be qualified by a table name, view name, or alias. The table name cannot be used if an alias is defined.

When a derived table, rowset or table-valued function, or operator clause (such as PIVOT or UNPIVOT) is used, the required table_alias at the end of the clause is the associated table name for all columns, including grouping columns, returned.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37812953
ashok111,

The alias is required when using T-SQL.  Period.

Let's move on.
0
 
LVL 9

Expert Comment

by:damerval
ID: 37813025
acperkins:
I have used subqueries to extract column values or criteria many times in SQL server, for years, without ever needing an alias. For instance:

select col1, col2, col3 = (subselect...) FROM table...
or
select col1, col2 from table where col1 = (subselect....)

Therefore, on top of being condescending, categorical and obnoxious, you are also wrong.
Please respect the other experts on this forum. We are here to help the users, not to insult one another. Your tone has no place in this exchange.

Ashok111:
Despite what you may read here, things are clearly more complex than they appear. I'm happy to do some research if you're really interested.
Thanks!

Philippe
Philippe
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37813569
Philippe,

I have used subqueries to extract column values or criteria many times in SQL server, for years, without ever needing an alias.
I never mentioned subqueries, and I am afraid you are not using a subquery.  It is a derived table that is why you need an alias.

Therefore, on top of being condescending, categorical and obnoxious, you are also wrong.
Before making a fool of yorself, please take the time to test.  I promise it will not take you 10 seconds.  As you can see everyone else has done it already and are now convinced.  Unfortunately, certainty makes me categorical and stuborness to realize you are wrong makes me resort to being condescending.  So I will apologize, if you can tell me what would be an appropriate way to demonstrate it to you.  I have tried posting the error message, I have tried documentation, what else can I possibly do?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37813655
Your tone has no place in this exchange.
If you feel any of my comments are inappropriate, take it up in Community Support and lets just discuss the facts here.

P.S.  Most people here call me by my name: Anthony
0
 
LVL 3

Expert Comment

by:Chris__W
ID: 37813715
Wow, my homework eh?  Copying faulty code and going soley by what SSMS lists as the issue is not what I'd consider 'homework'.  You may want to look past that, and it might take more than 10 seconds.  Surely you're aware that an extra or missing ')' will get you that same type of error, right?

If you've written actual code, you're also aware that this statement has issues:

"The alias is required when using T-SQL.  Period."

Excellent tact by the way.  I see you pass on these types of quality responses regularly here.  Regardless, if he's happy with the 'b', I'm sure you'll get your assist points (especially for the copy-paste steps) and can move on...  Clearly he'll have better results with actually fixing his code by working with damerval.  I'm going to try and forget this chain altogether, as just typing this wasted even more time.

Good luck with the code Ashok111.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37813750
Grief!  All EE is asking is that when we post solutions we test them.  Clearly the solution posted was not tested and in fact fails to compile. The author did the correct thing by pointing that out and kindly posting the correct solution for them and he/she is told they are wrong?  What is it with you guys?  Is it such an effort to test your code?  If you feel the code could have been improved (and I agree with you there) than post a better solution.  But in the meantime leave the author alone, they are right, they did the right thing.

Surely you're aware that an extra or missing ')' will get you that same type of error, right?
But that is not the case here.  See for yourself.  The author did not add or substract any, all they did was add the missing alias.
0
 
LVL 3

Expert Comment

by:Chris__W
ID: 37814171
I can't beleive I'm going to type another reply, but here it is.   Feel free to reply with whatever bs you'd like, as I'm closing this tab and the monitoring is already off.

Nice redirect - No one has any issue with the author, not only did he get it working -  he even used an alias to begin with.  I've not seen many cases, outside of a derived table in conjunction with a UNION statement, where an alias actually caused a problem.  However, if that's not the core of the issue, they may be moving on with code that has more fundamental issues.  Something damerval is offering to help with.

Redirecting attention from the issues with your tone and remarks doesn't really help though when I can still scroll up and read them.  Quotes are neat though....

Here's a fun one you might have thought about earlier on:
and lets just discuss the facts here.
0
 
LVL 13

Author Comment

by:Ashok
ID: 37814217
acperkins,

You are right.  I tested it with SQL Server 2000.
And it did not work without fixing it a little.

I also said that alias may or may not be needed in SQL Server 2008.  I still have to confirm it which I will do later at home.
I have to create the table, insert data and test the same SELECT without the alias "b".

Thnaks,
Ashok
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 13

Author Comment

by:Ashok
ID: 37814312
Hell everyone,

In SQL Server 2008......

I just tried.
I did not even have to execute the SELECT statement to figure out if alias "b" is needed or NOT.

After just copying and pasting it, SQL Server Manager Studio gave error immediately on last closing ")".
The exact error is
Incorrect syntax near ")". Expecting AS, ID or QUOTED_ID.

By the way, I ignored the error and click the Execute

and

Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ')'.

Above error showed up.

This confirms that it is not only SQL 2000 that requires the alias "b".

Thanks,
Ashok
0
 
LVL 13

Author Comment

by:Ashok
ID: 37814335
And here is the screenshot
from
SQL Server (2008) Manager Studio.
Proof.png
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37814524
ashok111,

This confirms that it is not only SQL 2000 that requires the alias "b".
Exactly, it is required and always has been.  Perhaps I should have done the same thing (posted a screenshot from each environment) when i originally tested it and there would not have been any misunderstanding.

I am sorry you had to go through all this and glad you figured it out.

Best of luck.
0
 
LVL 13

Author Comment

by:Ashok
ID: 37814564
acperkins,

I feel like you deserve some points.
For me, it's not about points, but learning exactly what is CORRECT.  Knowing if there are any differences between SQL 2000 & SQL 2008 is very important for me so my time is not wasted.

Thanks,
Ashok
0
 
LVL 13

Author Comment

by:Ashok
ID: 37814581
acperkins,

Let me know if you like me to object and give some points to you.

Thanks,
Ashok
0
 
LVL 7

Expert Comment

by:Cboudroz
ID: 37814615
If the goal is to show the Month that got the Highest count(DISTINCT Chart_No)

You only need to use TOP 1, you don't need sub select in the where clause!!!!  

This is the Correct way (better query plan and less IO) :

select top 1
Month(last_date) as [Mth],
Count(distinct Chart_No) as [ChartCount]
from Treatment
where last_date >= '2011-01-01'
and last_date < '2012-01-01'
Group by Month(last_date)
order by 
 ChartCount desc

Open in new window

0
 
LVL 9

Expert Comment

by:damerval
ID: 37814619
Ashok:
I agree about awarding the points to acperkins. Although he was clearly disrespectful with us, he found the whole answer for you and that is what matters. I learned something about SQL server today which, despite my years of working with this DBMS, I had never encountered.

Anthony:
There are many other ways in which I, or any respectful person, could have gone about making the points you made, without insulting the others. If you think being wrong makes one look foolish... I can't really express how mistaken that is. More and more, what keeps people like you and me in this profession is not the extent to which we are always right, so much as our ability to detect when we are wrong, and relate to people who have the wrong idea so as to collectively arrive at the right idea. Projects and deliverables are not being cranked out by single experts anymore, they're being produced by teams. One of the primary qualities needed to be part of a team is respect, and you did not demonstrate that to Chris and I today.
For me the really interesting question here is how, after working with SQL server for over 10 years, and producing some pretty complex artifacts, I never knew this. I think I never encountered this SQL server-specific limitation of T-SQL because I always go for the holistic FROM clauses, putting the logic in the joins themselves. Coupled with my continued attempt at writing SQL that will execute in the same way in as many DBMS systems as possible that may not implement this limitation (I know informix and Oracle are two examples, I'm not sure about other databases), I can't really explain my missing what seems to be a ubiquitous characteristic of SQL server for all these years in any other way.
0
 
LVL 13

Author Comment

by:Ashok
ID: 37814646
Cboudroz,

Very clever.

I do not have many records in my test table so both SQL runs in 0.00 seconds.
But yours has only 6 rows with Query PLAN vs 13 rows.

Does less rows necessarily mean it is faster?

Thanks,
Ashok
0
 
LVL 13

Author Comment

by:Ashok
ID: 37814664
Cboudroz,

Using your solution with
SET STATISTICS IO ON

Table 'Treatment'. Scan count 1, logical reads 1
vs
Table 'Treatment'. Scan count 2, logical reads 2

so it looks like double speed (with your solution) especially when there are many records.

Thanks,
Ashok
0
 
LVL 7

Expert Comment

by:Cboudroz
ID: 37814665
Less IO mean faster... but I just realize a big difference whit my solution...

If you have two month with a egal number of MAX(Count(distinct Chart_No))

My query will only show one row, but the other solution will show both row.

...

!!
0
 
LVL 13

Author Comment

by:Ashok
ID: 37814683
Cboudroz,

This my sample data.

Chart_No      Proc_Code      Last_Date
12345           ABC1                  2011-01-22 00:00:00.000
12345           ABC2                  2011-08-22 00:00:00.000
12345           ABC3                  2011-03-22 00:00:00.000
12347           ABC2                  2011-04-22 00:00:00.000
12347           ABC3                  2011-05-22 00:00:00.000
12348           ABC1                  2011-08-22 00:00:00.000
12345           ABC1                  2011-01-22 00:00:00.000

In above I have August 2 times, but with damerval or your solution, I get exactly one record in result.

Mth      ChartCount
8      2

Remember, this is yearly data so there cannot be more than one same month.

Do you mean something else?

Thanks,
Ashok
0
 
LVL 7

Expert Comment

by:Cboudroz
ID: 37814689
if your having twice two date

like this:
Chart_No      Proc_Code      Last_Date
12345           ABC1                  2011-01-22 00:00:00.000
12345           ABC2                  2011-01-22 00:00:00.000
12345           ABC3                  2011-03-22 00:00:00.000
12347           ABC2                  2011-03-22 00:00:00.000
0
 
LVL 9

Expert Comment

by:damerval
ID: 37814694
Quite right Choudroz!
Ashok, when two months share first place, do you need two rows, or just the most recent month? If the latter, Choudroz's expression can be tweaked a little by adding the month(last_date) value to the ORDER BY clause like this: ORDER BY ChartCount DESC, month(last_date) DESC.
If you need both rows, revert to the previous statement.
0
 
LVL 13

Author Comment

by:Ashok
ID: 37814710
I do have twice two date in my sample data.

2011-08-22 00:00:00.000

Above date is 2 times.

but I will get only 1 record (not 2).

Even with updated data like this

Chart_No      Proc_Code      Last_Date
12345           ABC1                  2011-01-22 00:00:00.000
12345           ABC2                  2011-08-22 00:00:00.000
12345           ABC3                  2011-03-22 00:00:00.000
12347           ABC2                  2011-09-15 00:00:00.000
12347           ABC3                  2011-09-15 00:00:00.000
12348           ABC1                  2011-08-22 00:00:00.000
12345           ABC1                  2011-01-22 00:00:00.000

I think it is because of DISTINCT, it will not return two.

Thanks,
Ashok
0
 
LVL 13

Author Comment

by:Ashok
ID: 37814713
I need ONE row.

Ashok
0
 
LVL 13

Author Comment

by:Ashok
ID: 37814718
damerval,

With your solution, and repeated DATE (twice same date with same Chart_No or different Chart_No(s)), I get
one record result which is what I am expecting.

Thanks,
Ashok
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37814753
ashok111,

I feel like you deserve some points.
Not at all.  You arrived at the solution that worked for you with the help of Philippe.  You assigned the points correctly.

Philippe,
Point well made and I stand corrected.  I was frustrated at my apparent lack of communication skills at explaining something that I thought was easy to see.  I realize now that it was a misuderstanding that could have easily resolved with some screenshots to explain the syntax error.

Cboudroz,
As I alluded to previously I thought it was not the best solution, however my best attempts were using a CTE which of course is verboten in SQL Server 2000.  But I think you are on the right track.


All,
I am glad we have now got back what we do best here:  Share well thought out and proven technical ideas.  No one in their right mind is here for the points and we all have too much to learn to waste time on side-trips.

Best.
0
 
LVL 13

Author Comment

by:Ashok
ID: 37814767
I would like to assign points to
Cboudroz
for best solution.

Thanks
Ashok
0
 
LVL 7

Expert Comment

by:Cboudroz
ID: 37816247
Happy to help!!

thanks!
0
 
LVL 13

Author Comment

by:Ashok
ID: 37819948
Could you re-open this question?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37820312
ashok111,

As I stated before, I believe that damerval gave you a solution in good faith and you accepted it.  If you (or damerval for that matter) now feel the points awarded should change and you feel bound and determined about that, then you can object or post a message in Community Support to have the question opened.   Otherwise, I think we should leave it as is.
0
 
LVL 13

Author Closing Comment

by:Ashok
ID: 37826773
Missing a character "b".
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now