|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
sql statement (how to)result by sql statement, any suggestion ? Thanks in advance. Sample records number Year qty --------- ------- ---------- 1000 2006 10 1000 [null] 20 2000 [null] 30 2000 2006 40 Expected results number Year qty --------- ------- ---------- 1000 2006 30 2000 2006 70 I tried this sql statement, but I got unexpected result. select first(number), first(year), sum(qty) from table group by number Unexpected results number Year qty --------- ------- ---------- 1000 2006 30 2000 null 70 The key point is how to get the records which is not null for the column of year. Try:
SELECT Number , Year = max( year ) , Qty = sum( qty ) FROM Table GROUP BY Number , Year -- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous Show quote "sampras" <sampras***@hotmail.com> wrote in message news:OcJe0Up1GHA.5048@TK2MSFTNGP05.phx.gbl... >I have a table with following sample records, How to select the desired > result by sql statement, any suggestion ? Thanks in advance. > > > Sample records > number Year qty > --------- ------- ---------- > 1000 2006 10 > 1000 [null] 20 > 2000 [null] 30 > 2000 2006 40 > > > Expected results > number Year qty > --------- ------- ---------- > 1000 2006 30 > 2000 2006 70 > > > I tried this sql statement, but I got unexpected result. > select first(number), first(year), sum(qty) > from table > group by number > > Unexpected results > number Year qty > --------- ------- ---------- > 1000 2006 30 > 2000 null 70 > > The key point is how to get the records which is not null for the column of > year. > > > > > > > > > > it does nothing. because the condition (group by number and year) will treat them four different records.
"Arnie Rowland" <ar***@1568.com> 撰寫於郵件新èž:OP4Dq0p1GHA.4***@TK2MSFTNGP04.phx.gbl... SELECT Try: Number , Year = max( year ) , Qty = sum( qty ) FROM Table GROUP BY Number , Year -- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous Show quote "sampras" <sampras***@hotmail.com> wrote in message news:OcJe0Up1GHA.5048@TK2MSFTNGP05.phx.gbl... >I have a table with following sample records, How to select the desired > result by sql statement, any suggestion ? Thanks in advance. > > > Sample records > number Year qty > --------- ------- ---------- > 1000 2006 10 > 1000 [null] 20 > 2000 [null] 30 > 2000 2006 40 > > > Expected results > number Year qty > --------- ------- ---------- > 1000 2006 30 > 2000 2006 70 > > > I tried this sql statement, but I got unexpected result. > select first(number), first(year), sum(qty) > from table > group by number > > Unexpected results > number Year qty > --------- ------- ---------- > 1000 2006 30 > 2000 null 70 > > The key point is how to get the records which is not null for the column of > year. > > > > > > > > > > So, just remove the Year from the GROUP BY.
-- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Sampras" <sampras***@hotmail.com> wrote in message news:OwDDN4t1GHA.4752@TK2MSFTNGP05.phx.gbl... it does nothing. because the condition (group by number and year) will treat them four different records. "Arnie Rowland" <ar***@1568.com> 撰寫於郵件新èž:OP4Dq0p1GHA.4***@TK2MSFTNGP04.phx.gbl... Try:SELECT Number , Year = max( year ) , Qty = sum( qty ) FROM Table GROUP BY Number , Year -- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous Show quote "sampras" <sampras***@hotmail.com> wrote in message news:OcJe0Up1GHA.5048@TK2MSFTNGP05.phx.gbl... >I have a table with following sample records, How to select the desired > result by sql statement, any suggestion ? Thanks in advance. > > > Sample records > number Year qty > --------- ------- ---------- > 1000 2006 10 > 1000 [null] 20 > 2000 [null] 30 > 2000 2006 40 > > > Expected results > number Year qty > --------- ------- ---------- > 1000 2006 30 > 2000 2006 70 > > > I tried this sql statement, but I got unexpected result. > select first(number), first(year), sum(qty) > from table > group by number > > Unexpected results > number Year qty > --------- ------- ---------- > 1000 2006 30 > 2000 null 70 > > The key point is how to get the records which is not null for the column of > year. > > > > > > > > > > sampras wrote:
Show quote > I have a table with following sample records, How to select the desired Does the number correspond to the year? Will 1000 number be in a year> result by sql statement, any suggestion ? Thanks in advance. > > > Sample records > number Year qty > --------- ------- ---------- > 1000 2006 10 > 1000 [null] 20 > 2000 [null] 30 > 2000 2006 40 > > > Expected results > number Year qty > --------- ------- ---------- > 1000 2006 30 > 2000 2006 70 > > > I tried this sql statement, but I got unexpected result. > select first(number), first(year), sum(qty) > from table > group by number > > Unexpected results > number Year qty > --------- ------- ---------- > 1000 2006 30 > 2000 null 70 > > The key point is how to get the records which is not null for the column of > year. 2006, always? If not, I don't think you can get the desired result. > Does the number correspond to the year? Will 1000 number be in a year NO, the number does not correspond to the year, it is a transaction number > 2006, always? If not, I don't think you can get the desired result. only, each transaction consists of records with same number, but only one of them have full details information, such as year, I need to group them and sum the amount in my case. I aslo tried this sql. Sample records >> number Year qty Staff (New column) select number, year, qty, staff>> --------- ------- ---------- ------ >> 1000 2006 10 A >> 1000 [null] 20 >> 2000 [null] 30 >> 2000 2006 40 B from table order by number, year desc the result number Year qty staff --------- ------- ---------- -------- 1000 2006 10 A 1000 [null] 20 2000 2006 40 B 2000 [null] 30 so I tried this sql based on the above result select first(number), first(year), sum(qty), first(staff) from ( select number, year, qty, staff from table order by number, year desc ) group by number but the result is still >> Unexpected results Is seems that I could not get the correct first record for each grouping, >> number Year qty Staff >> --------- ------- ---------- ------- >> 1000 2006 30 A >> 2000 [null] 70 [null] Any suggestion, Hope you can understand. Thanks a lot. "Ken" <kshap***@sbcglobal.net> ???????:1158088228.222887.68***@e63g2000cwd.googlegroups.com...Show quote > > sampras wrote: >> I have a table with following sample records, How to select the desired >> result by sql statement, any suggestion ? Thanks in advance. >> >> >> Sample records >> number Year qty >> --------- ------- ---------- >> 1000 2006 10 >> 1000 [null] 20 >> 2000 [null] 30 >> 2000 2006 40 >> >> >> Expected results >> number Year qty >> --------- ------- ---------- >> 1000 2006 30 >> 2000 2006 70 >> >> >> I tried this sql statement, but I got unexpected result. >> select first(number), first(year), sum(qty) >> from table >> group by number >> >> Unexpected results >> number Year qty >> --------- ------- ---------- >> 1000 2006 30 >> 2000 null 70 >> >> The key point is how to get the records which is not null for the column >> of >> year. > > Does the number correspond to the year? Will 1000 number be in a year > 2006, always? If not, I don't think you can get the desired result. > Sampras,
Don't use the database like a spreadsheet. You need two tables to do this properly, one with one row per 'number' with details and another with the line items. Fake DDL: create table dbo.TB_Transaction cTransactionNumber(int) (PK) cYear (int) cStaff create index NIX_TB_Transaction_cYear on dbo.TB_Transaction.cYear Create table dbo.TB_TransactionDetail cTransactionNumber(int) (PK1,FK) cTransactionItemNumber(int) (PK2) cQuantity (int) select distinct(T.cTransactionNumber) ,T.cYear ,sum(TB.cQuantity) from dbo.TB_Transaction as T join dbo.TB_TransactionDetail as TD on TD.cTransactionNumber = T.cTransactionNumber group by T.cTransactionNumber order by T.cTransactionNumber ,T.cYear desc -- makes no sense as it likely contradicts cTransactionNumber Show quote "Sampras" wrote: > > Does the number correspond to the year? Will 1000 number be in a year > > 2006, always? If not, I don't think you can get the desired result. > > NO, the number does not correspond to the year, it is a transaction number > only, each transaction consists of records with same number, but only one > of them have full details information, such as year, I need to group them > and sum the amount in my case. I aslo tried this sql. > > > Sample records > >> number Year qty Staff (New column) > >> --------- ------- ---------- ------ > >> 1000 2006 10 A > >> 1000 [null] 20 > >> 2000 [null] 30 > >> 2000 2006 40 B > > select number, year, qty, staff > from table > order by number, year desc > > the result > number Year qty staff > --------- ------- ---------- -------- > 1000 2006 10 A > 1000 [null] 20 > 2000 2006 40 B > 2000 [null] 30 > > so I tried this sql based on the above result > > select first(number), first(year), sum(qty), first(staff) > from > ( > select number, year, qty, staff > from table > order by number, year desc > ) > group by number > > but the result is still > >> Unexpected results > >> number Year qty Staff > >> --------- ------- ---------- ------- > >> 1000 2006 30 A > >> 2000 [null] 70 [null] > > > Is seems that I could not get the correct first record for each grouping, > Any suggestion, Hope you can understand. > Thanks a lot. > > > "Ken" <kshap***@sbcglobal.net> > ???????:1158088228.222887.68***@e63g2000cwd.googlegroups.com... > > > > sampras wrote: > >> I have a table with following sample records, How to select the desired > >> result by sql statement, any suggestion ? Thanks in advance. > >> > >> > >> Sample records > >> number Year qty > >> --------- ------- ---------- > >> 1000 2006 10 > >> 1000 [null] 20 > >> 2000 [null] 30 > >> 2000 2006 40 > >> > >> > >> Expected results > >> number Year qty > >> --------- ------- ---------- > >> 1000 2006 30 > >> 2000 2006 70 > >> > >> > >> I tried this sql statement, but I got unexpected result. > >> select first(number), first(year), sum(qty) > >> from table > >> group by number > >> > >> Unexpected results > >> number Year qty > >> --------- ------- ---------- > >> 1000 2006 30 > >> 2000 null 70 > >> > >> The key point is how to get the records which is not null for the column > >> of > >> year. > > > > Does the number correspond to the year? Will 1000 number be in a year > > 2006, always? If not, I don't think you can get the desired result. > > > > > sampras wrote:
> I tried this sql statement, but I got unexpected result. I wasn't aware FIRST() was a function in SQL Server, so yes those> select first(number), first(year), sum(qty) > from table > group by number > > Unexpected results > number Year qty > --------- ------- ---------- > 1000 2006 30 > 2000 null 70 results are unexpected.... What happens when you try MIN() or MAX()? Oh, and I assume you can't have more than one Year for each Number? Otherwise you won't be able to determine which Year to map the NULLs to. Chris You can try:
SELECT t1.Number, t2.[Year], SUM(qty) as qty FROM Table t1 INNER JOIN (SELECT Number, [Year] FROM Table WHERE [Year] IS NOT NULL) as t2 ON t1.number = t2.Number GROUP BY t1.Number, t2.[Year] You should be able to get additional fields from the non-NULL year row similarly. I hope that this helps. Matthew Bando MatthewB @ InfoStrat dot com Show quote "sampras" wrote: > I have a table with following sample records, How to select the desired > result by sql statement, any suggestion ? Thanks in advance. > > > Sample records > number Year qty > --------- ------- ---------- > 1000 2006 10 > 1000 [null] 20 > 2000 [null] 30 > 2000 2006 40 > > > Expected results > number Year qty > --------- ------- ---------- > 1000 2006 30 > 2000 2006 70 > > > I tried this sql statement, but I got unexpected result. > select first(number), first(year), sum(qty) > from table > group by number > > Unexpected results > number Year qty > --------- ------- ---------- > 1000 2006 30 > 2000 null 70 > > The key point is how to get the records which is not null for the column of > year. > > > > > > > > > > >  |
|||||||||||||||||||||||
Other interesting topics