|
Its a simple sum() and Group By query
Select col1, col2, sum(col3), sum(col4) etc
Group By col1,col2
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Oh! yes such a simple case  I didn't get that idea. Thank you.
But, Any Idea again?
To get this data I had to use the following query, the query is already complicated.
Is there any way to Sum up the data as you suggested without dumping this data into temp Table?
NGB CCU 0 0 0 0 0 0 0 0 6
NGB CCU 0 0 0 0 0 0 4 0 0
SHA SIN 0 0 0 0 0 0 0 0 4
SHA SIN 0 0 0 0 0 0 0 1 0
SHA SIN 0 0 0 0 0 0 25 0 0
with myTable(POL,POD,Container_Code,qty) as
(Select Distinct POL,POD,Container_Code,SUM(qty) as 'Qty'
from TableF
Where account_name like '%Freight%' and account_name not like '%FREIGHT REBATE'
Group by POL,POD,Container_Code
)
select Distinct POL,POD,
(case when container_code like '%R%20' Then qty else '' end ) as 'R_D20',
0 as 'R_D40',
(case when container_code like '%HR%40' Then qty else '' end ) as 'R_H40',
(case when container_code ='FL20' OR container_code ='OT20' Then qty else '' end ) as 'OT_D20',
(case when container_code ='FL40' OR container_code ='OT40' Then qty else '' end ) as 'OT_D40',
0 as 'OT_H40',
(case when container_code='D20' Then qty else '' end ) as 'N_D20',
(case when container_code='D40' Then qty else '' end ) as 'N_D40',
(case when container_code='HC40' Then qty else '' end ) as 'N_H40'
From myTable
order by POL,POD
modified 27-Sep-11 3:55am.
|
|
|
|
|
Try this:
with myTable(POL,POD,Container_Code,qty) as
(Select Distinct POL,POD,Container_Code,SUM(qty) as 'Qty'
from TableF
Where account_name like '%Freight%' and account_name not like '%FREIGHT REBATE'
Group by POL,POD,Container_Code
)
select Distinct POL,POD,
Sum(case when container_code like '%R%20' Then qty else 0 end ) as 'R_D20',
0 as 'R_D40',
Sum(case when container_code like '%HR%40' Then qty else 0 end ) as 'R_H40',
Sum(case when container_code ='FL20' OR container_code ='OT20' Then qty else 0 end ) as 'OT_D20',
Sum(case when container_code ='FL40' OR container_code ='OT40' Then qty else 0 end ) as 'OT_D40',
0 as 'OT_H40',
Sum(case when container_code='D20' Then qty else 0 end ) as 'N_D20',
Sum(case when container_code='D40' Then qty else 0 end ) as 'N_D40',
Sum(case when container_code='HC40' Then qty else 0 end ) as 'N_H40'
From myTable
Group by POL,POD
order by POL,POD
Note that I changed your empty strings to 0 to avoid mixing of types.
|
|
|
|
|
Hi thank you for the reply.
When I use Group By POL,POD as your suggested
Get error for
"Container_Code and Qty is invalid in the select list because it is not contained in either an aggregate function or the Group By clause."
When I add these two in Group by Clause
Group by POL,POD,container_code,qty
Got the result as same as without using Group By.
|
|
|
|
|
Dear
You can use group by clause.
select c1, c2, isnull(sum(c3),0), isnull(sum(c4),0)
from tbl
group by c1, c2
use having clause for again filter your output. use it after grope by clause.
|
|
|
|
|
Thank you for your reply.
Group By doesn't work for my case.
|
|
|
|
|
hi
i want implementation data warehous on the sql server please guide
thanks
|
|
|
|
|
Buy a book, read some articles, SQL Server Central[^] is a good place to start.
It is way too big a subject to get a response in a forum thread.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have deleted some tables containing data and images from Database but database size is not reduced. any body guide me .Why not reduced?
|
|
|
|
|
If you are talking about Microsoft Access database, then you can use "compact and repair" to reduce the size of your file.
|
|
|
|
|
Why should it be reduced?
Most databases work in this way; you deleted items in the database, you didn't make the database smaller. Any deleted items aren't "physically" removed, they're just marked as being "non-existent". Imagine a database having to remove a single string from 5 trillion others, and save a concatenated form back again. Databases are fast by doing things efficiently.
No real "removals", but "marks". If you want to reclaim the space, you'll have to "shrink" your database. Access has it's way, as mentioned, and Sql Server has some sprocs.
Bastard Programmer from Hell 
|
|
|
|
|
Any body please guide from where I can download SQL Server 2008 ?
|
|
|
|
|
There are many versions, for modest projects I prefer SQL Server 2008R2 Express Edition, here[^]. For others, search the Microsoft site, or google.
|
|
|
|
|
Your thought please on this topic in the QA forum.
QA : Do we need SP's anymore?
Its the man, not the machine - Chuck Yeager
If at first you don't succeed... get a better publicist
If the final destination is death, then we should enjoy every second of the journey.
|
|
|
|
|
Caveat - I am VERY pro store procs, I not allow direct string access to the database.
I disagree with you're premise that they are simpler to debug in strings, this may be true if you have no TSQL experience but not if you have even a modicum of skill.
If you are doing fairly standard CRUD work then your arguments are absolutely valid. However if you are doing any serious volume manipulation then procedures are easier to build and tune. I can't imagine writing a 100 line proc as strings, it just would not make sense.
As for processing (complex, volume) data in the business layer this just does not work. We had a team use Enterprise library and do all the processing in the BL layer, they needed 2 application servers to match the DB server performance.
I do sympathise with your stand, I recently had to get involved in PLSQL, my skills are a decade out of date, what a bloody nightmare. I tweaked my code generator to write PLSQL CRUD procs and get an Oracle specialist to do anyhting more complex.
Also any (LOB) developer worth paying should know SQL and the database they are working on!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your input.
I agree there are times where they are absolutely necessary ( mostly server side heavy manipulations). My problem is they are being pushed where they aren't needed.
The problem with DB's (tables structures and SP's) is that they go rigid quickly and you dare not change them, I have had clients where 1500 SP's were quite common and no one knew how they worked.
Its the man, not the machine - Chuck Yeager
If at first you don't succeed... get a better publicist
If the final destination is death, then we should enjoy every second of the journey.
|
|
|
|
|
Mehdi Gholam wrote: where 1500 SP's were quite common and no one knew how they worked
Thats because the stupid bastards (management) don't retain the IP (as in the business knowledge) and the skills to manage their own data.
So many organisations feels they can outsource their development work, get a monkey to do the work and maintain a viable business plan - idiots.
And I'm a contractor who makes a very comfortable living from this business practice. Don't push that button I can go on for hours on this subject!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
finger poised... push... click...
Come on lets have some of your war stories!
Its the man, not the machine - Chuck Yeager
If at first you don't succeed... get a better publicist
If the final destination is death, then we should enjoy every second of the journey.
|
|
|
|
|
Mehdi Gholam wrote: Come on lets have some of your war stories!
Not a chance, both of us have better things to do 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mehdi Gholam wrote: server side heavy manipulations
All manipulations (other than formatting for display) should be server-side regardless of how the SQL gets there.
|
|
|
|
|
Mycroft Holmes wrote: I not allow direct string access to the database.
Then how do you execute the procedures? 
As to debugging the SQL, I see no difference -- either way, I try them out in SSMS.
Mycroft Holmes wrote: writing a 100 line proc
I've never seen one of those, sounds awful. On the other hand, I've written at least one Insert like:
SetCommand (
@"
INSERT INTO tablex
(
field0
,
field1
,
...
,
fieldn
)
VALUES
(
@Param0
,
@Param1
,
...
,
@Paramn
)
"
,
value0
,
value1
,
...
,
valuen
) ;
Which I guess could be a hundred lines.
|
|
|
|
|
PIEBALDconsult wrote: I not allow direct string access to the database
Read Table/Views instead of database - my error.
PIEBALDconsult wrote: either way, I try them out in SSMS.
Ok so you write the TSQL in SSMS and run it in the BL and yet you don't want to put the tested code into a proc but move it into the BL.
I actually agree with you when it come to the CRUD procs (I use a code generator and have not written an insert procedure for many years), even some simple queries could have a life in either format. A complex query with multiple joins, using temp or var tables and with interim results just will not go into code, it belongs in a proc.
As to the 100 lines crap you put up  bleh I write rather tight code and 100 lines on a complex query is not unusual, just before it is turned into a proc I run Red-Gates formatter over the code, it blows it out as above but makes it eminently more readable (and therefore supportable).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
It is well-known that I am basically anti-stored-procedure. Use them when you must, but not when you don't. (Currently the only time I must is when the boss insists. )
In the late 90s, when I first encountered SQL Server (6), we were accessing it via ODBC, and stored procedures were the only way to use transactions, so any code that altered the contents of the database had to be in a stored procedure (queries were not in stored procedures, I still boggle at people who insist on writing stored procedures for queries). I got to the client site to install the part of the system I supported and the client asked me to add a feature (a reasonable feature). It only involved a new column in a table and a small change to a stored procedure, but I didn't want to do that in the field without testing it. Unfortunately the program manager insisted, so I walked into the server room, started up enterprise mangler, added the column, and altered the stored procedure. It worked fine. Then I had to save the a script to add the column and the new version of the stored procedure to a floppy so I could put them into version control when I got back to the office -- that was fine, the plane didn't crash on my way back and the changes got into version control. But, twice when I was still with the company, and at least once since then, the stored procedure disappeared  !
My very biggest complaint about stored procedures (or any code stored in the database (it's a database, not a codebase)) is that it is too easy to change (or delete), either by mistake or by mischief.
On the other hand, making proper changes may be more difficult to make because you not only have to deploy new executables, but also ensure that the procedures and such are updated as well.
Whereas, with all the SQL in the executable, you only have to deploy the executables (after proper review and testing), and there's nothing in the database that allows anyone to alter production code.
And if you have multiple clients with customized versions of your software (which is my experience), you don't have to track custom procedures as well as custom program code.
It is my experience that stored procedures increase maintenance costs.
Where I am now, there are stored procedures and views galore, all of which could easily be in program code. One of my first tasks is to get them all into version control (the program code is already there). As part of that, I'm comparing them between dev, test, and prod. I have already found several procedures that won't execute because the tables have been altered, and some in prod that aren't in dev. But at least it pays well  .
|
|
|
|
|
About security: No real difference if you use parameterized queries.
About performance: When the query is compiled there should normally be no difference.
But, a stored procedure is precompiled, so the time to optimize and compile the query can be written off for the SP. This is sometimes a larger part of the execution time. (Yes, I know that most DBs nowadays cache the execution plans).
On the other hand, with a precompiled SP you will always have the same execution plan. This is obviously not always optimal. Think for example of the LIKE or IN clauses. They might need very different scanning of the indexes depending on the parameters supplied.
They lock you down to a vendor: So does dotnet.
And a couple of pros and cons from me:
Making dynamic SQL in a SP is a complete PITA IMAO.
Think of the roundtrip time from your application to the DBServer. This can sometimes be a serious bottleneck
|
|
|
|
|
Jörgen Andersson wrote: No real difference if you use parameterized queries.
Not true. You can set a DB so that the application has access ONLY to EXECUTING the stored procedures, if you exclusively use stored procedures. So having suddenly becomes much harder.
Otherwise you HAVE to give read and write access to the tables to your application - MUCH less secure.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
