SQL GROUPING Question

I have data similar to the following, that I would like to sum up.

code char(2)
amount decimal(12,2)
code_2 char(2)
code_3 char(15)


code    amount    code_2     code_3
01        100.00    NULL       15200.00
01        NULL       03            NULL
02        200.00      04            500.99
02        NULL       04            NULL
03        99.99        NULL      999.99
03        NULL      02             NULL
03        NULL      NULL        NULL

I would like to know if the data above is "group-able" so that I can get a
result set like this.

code    amount    code_2     code_3
01        100.00    03            15200.00
02        200.00    04             500.99
03        99.99      02             999.99

Because of the NULLS, I guess maybe it is not possible to group on those
columns?

Any help is much appreciated!!!

Thanks
Nick N.



0
Nick
12/9/2002 6:34:33 PM
sybase.ase.general 8655 articles. 0 followers. Follow

1 Replies
444 Views

Similar Articles

[PageSpeed] 14

How about something like:

select sum(isnull(amount,0)), max(isnull(code_2,'00')),
sum(isnull(code_3,0)) 
from mytable 
group by code

-bret


Nick wrote:
> 
> I have data similar to the following, that I would like to sum up.
> 
> code char(2)
> amount decimal(12,2)
> code_2 char(2)
> code_3 char(15)
> 
> code    amount    code_2     code_3
> 01        100.00    NULL       15200.00
> 01        NULL       03            NULL
> 02        200.00      04            500.99
> 02        NULL       04            NULL
> 03        99.99        NULL      999.99
> 03        NULL      02             NULL
> 03        NULL      NULL        NULL
> 
> I would like to know if the data above is "group-able" so that I can get a
> result set like this.
> 
> code    amount    code_2     code_3
> 01        100.00    03            15200.00
> 02        200.00    04             500.99
> 03        99.99      02             999.99
> 
> Because of the NULLS, I guess maybe it is not possible to group on those
> columns?
> 
> Any help is much appreciated!!!
> 
> Thanks
> Nick N.
0
Bret
12/9/2002 8:27:10 PM
Reply:

Similar Artilces:

GENERAL SQL SERVER: Question about the Script that setups the SQL Server Database.
This is the first time I have created a script for Sql Server 2000, and I have run into a few syntax ereas that I am not familar with. Bellow is a section of the script from the Portal server packages. At the end of the sample, is "ON [PRIMARY], Can anyone explain to me what this does and why the primary key isnt setup inside the code itself? I am basically trying to take parts of the script and incorrporate it into my own script and hate added syntax that I dont understand, makes debugging a real pain in the future. CREATE TABLE [dbo].[Portal_Announcements] ( [ItemID] [in...

general SQL question
--------------7EC95B4FEE051BD577C3D038 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I have a general SQL question. If there is a two-column table, e.g., id keyword -- ------ 1 k1 2 k2 1 k2 2 k3 (1) Is it possible to retrieve the values for id 1 by saying "select id from foo where keyword=k1 and keyword=k2" The above returns nothing. Since we are comparing against a single column (keyword), this doesn't seem possible to do the "and". (2) If...

general Sql question
Given the following tables Table1 --------- labnumber name doctor Table 2 --------- labnumber ailmentcode (hold 0 to many ailments for records in table 2) How can I get all the records in table 1 that have ailments (related in table 2 by labnumber) 1, 2, 3, 4, 5, 6, 7, 8, and 9. Thanks in advance Blake select table1.* from table1, table2 where table1.labnumber = table2.labnumber and table2.ailment in (1,2,3,4,5,6,7,8,9) Instead of the IN clause you could code and table2.ailment between 1 and 9 or even and table2.ailment >= 1 and table2.ailme...

SQL Grouping Question
I have a question about how to do a complex sorting of a SELECT query. Say we have the following rows in a table ID GroupID Name 1 1 A 2 2 Z 3 2 B 4 3 C 5 3 X 6 4 Y 7 5 F I want an SQL statement that will return the rows sorted by NAME with the caveat, and this is the complex part, that the groups stay together, sorted by NAME, with their place in the overall table set according to the first member of the group. So, in the above example, the re...

General SQL Question
 I am trying to create a query that will return the matching image name from one table or displays a null value for that column if there is no matching row. So for example I want to go through all the event table rows and match up the row information with image_name from Image Gallery table. If nothing matches up from that row I want a null value displayed. I been working on this for a while and all I manage to come up with is only returning rows that have matching IDs from Image Gallery and Event.  --------Image Gallery----------Image Name |  EventIDBaseball  &...

group by sql question
hey, i have a table of all the pages viewed by the users of my site. and im trying to summarize their viewing history. Im trying to group together the pages by the saved url. My table is set up like this : HistoryID Page Moment UserID i need to get out the usersid and distinct pages by the time they were viewed. for example if the data in the table looks like this : 1, page1.aspx, 5/05/09 2.35, 1 2, page2.aspx, 5/05/09 2.34, 1 3, page1.aspx, 5/05/09 2.33, 1 it would return the page, userid and number of views by the 'moment' they were viewed like this : page1.asp...

General SQLA/SQL question
I am curious if there is a way to create a temp table with the exact same structure as an existing table in one step. In other words, I don't want to either create the table by selecting the type and name of column from the systables or to explicitly create the table ahead of time and then populate it by selecting from the existing table. I am looking for something that would be similar to CREATE TEMPORARY TABLE X_TEMP AS SELECT * FROM X. I know that is not the correct syntax, but you get the idea. TIA, JM. Now this would be nice! Don't forget the 'on commit...

General question about inline sql
I'm executing inline sql (PB 10.1) against a MS SQL server database as per the documentation and i'm getting null in the 'into' value. The select will work in the DB painter. Have there been changes in the last few versions of PB regarding this. I've never had this problem before. Mark wrote: > I'm executing inline sql (PB 10.1) against a MS SQL server > database as per the documentation and i'm getting null in > the 'into' value. The select will work in the DB painter. > Have there been changes in the last few versions of PB > r...

db-sql general question
I have some common questions reg sql and db operataions..i have googled on this and found the overwhelming information,i understand some and confused abt someother ..if someone can give me all the answers at one place briefly,i wud really appreciate that...i am usign SQL server 2005 and i have DAL(ADO.NET) BLL set up in my  C#  web application 1-whats the significance of 'RETURN' in SQL? i mean,what will happen if i don't have it .When does having RETURN  matter at the end of SQL? 2-i am using the OUTPUT parameter,to get the id of inserted row.However in case of...

General Question about SQL efficiency
I have a website that is probably going to hold a sizable amount of data.  The data will be specific to groups of users based on login credentials.  Would it be more efficient to create a whole new database for each group of users, or create new tables for the groups in the existing database?  Any thoughts on the topic would be appreciated. ThanksJayMan I'm not quite sure what you consider a sizable amount of data, but one database should be fine.  If you feel that the database is beginning to act slow you can always partition tables, re-evaluate indexes set up o...

Group for general programming questions + more
I was wondering, and I would like to ask the admnistratrors of the newsgroups, if they plan to start a new newsgroup concerning system and application programming, or is this need already covered by grc.techtalk or any other group? And moreover, is GRC thinking of expanding the areas of discussions on Information technology by adding different and vasious groups beyond the existing ones? I truly think grc community is well organised and full of information wealth, but it could be more appealing to the IT world if more subjects relevant to IT were added and supported. I would li...

Group for general programming questions + more
I was wondering, and I would like to ask the admnistratrors of the newsgroups, if they plan to start a new newsgroup concerning system and application programming, or is this need already covered by grc.techtalk or any other group? And moreover, is GRC thinking of expanding the areas of discussions on Information technology by adding different and vasious groups beyond the existing ones? I truly think grc community is well organised and full of information wealth, but it could be more appealing to the IT world if more subjects relevant to IT were added and supported. I would li...

Simple SQL Question
Looking for you Classicly Trained Database Artists to give me Your opinion on this scenario: - 10 Balances to track in a Master Row. - 3 different Transaction History tables, some columns of which can only be determined by client logic (another loooong argument we'll pass on), ie, requiring a parameterized insert by the client. - Each History table has Begin and End Balance columns for the 10 balances. BALANCE_1 needs to be incremented by TRANS_AMT 100, it is currently 500. SELECT BALANCE_1, which returns 500, issue the UPDATE, and of course some guy comes along and decr...

Simple SQL Timestamp/GROUP BY question
hello, 5.5.4 SQLA. Build 1894. trying to do something like this: select colA, colB, colC, Date(colTimestamp) as SysDate, from table table table where <restrictions> group by colA, colB, colC and ((the Converted ColTimestamp)). i want to group by the date that i get from the conversion, so all my transactions for june 1 are together, then june 2, etc. However, i get the error (if i refer to the alias in my group by) "colTimestamp must appear in the group by clause". But i don't want to group by the *timestamp*, but rather by the resulting date afte...

Web resources about - SQL GROUPING Question - sybase.ase.general

European Grouping of Territorial Cooperation - Wikipedia, the free encyclopedia
An EGTC must have members from at least two member states and members can include local or regional authorities, bodies "governed by public law ...

SnapRecall 3D - Photo Sphere with Time Grouping on the App Store on iTunes
Get SnapRecall 3D - Photo Sphere with Time Grouping on the App Store. See screenshots and ratings, and read customer reviews.

Google Not Grouping Results? - Flickr - Photo Sharing!
More at www.seroundtable.com/archives/016794.html

YouTube - Windows 8: Semantic Zoom and Grouping Apps
Veröffentlicht am 05.04.2012 CIO.com's Shane O'Neill shows you how to zoom in and out to get a better view of your apps, and how to group multiple ...

Anachronistic UN electoral groupings need an overhaul
Anachronistic UN electoral groupings need an overhaul

Wanderers v Al Hilal: Tony Popovic praises his nervous players for re-grouping during ACL final
THIS is a final only halfway done, and the body language of the two coaches betrayed the tension still to be played out before the winner is ...

Let's give it a chance, says business grouping
Let's give it a chance, says business grouping

How to Hang a Perfect Wall Grouping (or Frame Cluster)
Bring out the hammer and start with the anchor piece. Hang on one side, then the other, then again and again, while keeping a sharp eye on balance. ...

Reeder for iOS updated with improved navigation, grouping by feed & multiple bug fixes
... left/right to go forward and back. A loading progress indicator has also been added to the in-app browser. Smart streams now support grouping ...

Reeder 2 Update Brings Feed Grouping, Themes And More
... and Fever. Soon after its release, it was also chosen as our AppAdvice App of the Week . Reeder 2 Reeder 2 Update Brings Feed Grouping, Themes ...

Resources last updated: 1/21/2016 5:44:05 AM