A rather basic question I am sure...
in [Programming SQL]
|
From: AussieRules on 20 Jun 2010 05:09 Hi, Firstly I am not a SQL person.. so I am sure that this question is rather basic.. for that I am sorry.. I have been given the task to create a few reports, and with this I need to group some sales data. The table looks like : transID OrderID Orderdate sku qty 1 1234 1/1/2010 12:12am ABC 3 2 1234 2/1/2010 8:19pm DEF 5 3 5678 1/1/2010 2:10pm ABC 3 4 5678 1/1/2010 2:10pm AFE 3 5 5678 1/1/2010 2:10pm FEW 3 6 5678 1/1/2010 2:10pm DEF 3 and so on.. I have just made up the data in this post, so if the date structure is not perfect sorry, but its a datetime, so has both the date and time part. So I want to have three reports. The first shows the totaly qty of an item sold for each day (for as much data as the table holds), the second is the same report, but shows the values by month. I have figured it so far to be Select sum(qty), sku, Orderdate from t_transaction group by sku, orderdate,qty... That seems to get back the data, but the date bit is including the time... and I just want them grouped by day (or month)... how can I do this... Also the third report, will just say what are the most common other items orders when this item is ordered... So if I look at SKU item ABC, the item DEF would be at the top of this result, as DEF was also ordered on both orders that ABC was. I would also get AFE and FEW but lower in the list as they are only on one order.. This one I have no idea how to go about... Thanks for you help on this... Let my learning begin...
From: Erland Sommarskog on 20 Jun 2010 06:00 AussieRules (nospam(a)nospam.com) writes: > Firstly I am not a SQL person.. so I am sure that this question is rather > basic.. for that I am sorry.. > > I have been given the task to create a few reports, and with this I need > to group some sales data. And you accepted, despite that you have sufficient training? > > So I want to have three reports. The first shows the totaly qty of an item > sold for each day (for as much data as the table holds), the second is the > same report, but shows the values by month. > > I have figured it so far to be Select sum(qty), sku, Orderdate from > t_transaction group by sku, orderdate,qty... > > That seems to get back the data, but the date bit is including the time... > and I just want them grouped by day (or month)... how can I do this... Per day use convert(char(8), Orderdate, 112) To get per month, change char(8) to char(6) > Also the third report, will just say what are the most common other items > orders when this item is ordered... > > So if I look at SKU item ABC, the item DEF would be at the top of this > result, as DEF was also ordered on both orders that ABC was. I would also > get AFE and FEW but lower in the list as they are only on one order.. Something like: SELECT TOP 5 sku FROM (SELECT a.sku, COUNT(*) AS a.cnt FROM orderdetails a WHERE a.item <> @item AND EXISTS (SELECT * FROM orderdetails b WHERE a.OrderID = b.OrderID AND a.item = @item)) AS x ORDER BY cnt DESC Since you did not post CREATE TABLE statement for you table and INSERT statements with the sample data, I did not care to do this myself, and the above is not tested or checked for correct syntax. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: John Bell on 20 Jun 2010 06:50 On Sun, 20 Jun 2010 19:09:54 +1000, "AussieRules" <nospam(a)nospam.com> wrote: >Hi, > >Firstly I am not a SQL person.. so I am sure that this question is rather >basic.. for that I am sorry.. > >I have been given the task to create a few reports, and with this I need to >group some sales data. > >The table looks like : > >transID OrderID Orderdate sku qty >1 1234 1/1/2010 12:12am ABC 3 >2 1234 2/1/2010 8:19pm DEF 5 >3 5678 1/1/2010 2:10pm ABC 3 >4 5678 1/1/2010 2:10pm AFE 3 >5 5678 1/1/2010 2:10pm FEW 3 >6 5678 1/1/2010 2:10pm DEF 3 > >and so on.. I have just made up the data in this post, so if the date >structure is not perfect sorry, but its a datetime, so has both the date and >time part. > >So I want to have three reports. The first shows the totaly qty of an item >sold for each day (for as much data as the table holds), the second is the >same report, but shows the values by month. > >I have figured it so far to be Select sum(qty), sku, Orderdate from >t_transaction group by sku, orderdate,qty... > >That seems to get back the data, but the date bit is including the time... >and I just want them grouped by day (or month)... how can I do this... > > >Also the third report, will just say what are the most common other items >orders when this item is ordered... > >So if I look at SKU item ABC, the item DEF would be at the top of this >result, as DEF was also ordered on both orders that ABC was. I would also >get AFE and FEW but lower in the list as they are only on one order.. > >This one I have no idea how to go about... > >Thanks for you help on this... > >Let my learning begin... > Hi When you want to post examples then you should say which version of SQL Server you are using along with the DDL for the table definitions and the example data as insert statements see http://www.aspfaq.com/etiquette.asp?id=5006. This makes it much easier to understand you problem and test possible solutions. You have the same ORDERID with different dates, which doesn't make immediate sense. If you are using SQL 2008 there is a DATE datatype which you can CAST the datetime to. If you are working on version prior to 2008 you can do the following to remove the time portion CAST(FLOOR(CAST(orderdate AS Float)) E.g. CREATE TABLE #orders ( id int not null identity, orderdate datetime , qty int not null ) INSERT INTO #orders ( orderdate, qty ) SELECT '20100101 09:01:20', 13 UNION ALL SELECT '20100102 10:00:00', 42 UNION ALL SELECT '20100103 10:03:00', 12 UNION ALL SELECT '20100103 10:05:00', 32 UNION ALL SELECT '20100104 09:10:00', 13 UNION ALL SELECT '20100104 10:30:00', 14 SELECT CAST(orderdate AS DATE) AS [OrderDate], SUM(qty) AS [TotalQty] FROM #orders GROUP BY CAST(orderdate AS DATE) SELECT CAST(FLOOR(CAST(orderdate AS Float)) AS DATETIME) AS [OrderDate], SUM(qty) AS [TotalQty] FROM #orders GROUP BY CAST(FLOOR(CAST(orderdate AS Float)) AS DATETIME) John
From: AussieRules on 20 Jun 2010 07:04 Hi, Thanks for your help.. Yes I took it on, but because I figured I could learn... no hard having a go.. The select now works, and the date string is returned as the value '20100614'. I assume that this 14/6/2010 (uk format).. Is there a way to convert this char, into a date format, so that the client VB.net application can work with date datatypes ? (I am binding a chart control to my dataset, so have to have the values returned from the SQL server as a date as I can't convert in the client app) Also sorry for not posting the SQL on the last request. I modified the code to suit my db, and ended up with : SELECT TOP 5 sku FROM (SELECT a.sku, COUNT(*) AS cnt FROM tbl_pick a WHERE a.sku <> '8845' AND EXISTS (SELECT * FROM tbl_pick b WHERE a.job_id = b.job_id AND a.sku = '8845')) AS x ORDER BY cnt DESC However I get the following: Msg 8120, Level 16, State 1, Line 3 Column 'tbl_pick.sku' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. I can see that the error comes from the select statement that has the count(*) code, but not sure where to put the group by clause.... Again, thanks heaps for your help on this.. "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D9D7A3112011Yazorman(a)127.0.0.1... > AussieRules (nospam(a)nospam.com) writes: >> Firstly I am not a SQL person.. so I am sure that this question is rather >> basic.. for that I am sorry.. >> >> I have been given the task to create a few reports, and with this I need >> to group some sales data. > > And you accepted, despite that you have sufficient training? > >> >> So I want to have three reports. The first shows the totaly qty of an >> item >> sold for each day (for as much data as the table holds), the second is >> the >> same report, but shows the values by month. >> >> I have figured it so far to be Select sum(qty), sku, Orderdate from >> t_transaction group by sku, orderdate,qty... >> >> That seems to get back the data, but the date bit is including the >> time... >> and I just want them grouped by day (or month)... how can I do this... > > Per day use > > convert(char(8), Orderdate, 112) > > To get per month, change char(8) to char(6) > >> Also the third report, will just say what are the most common other items >> orders when this item is ordered... >> >> So if I look at SKU item ABC, the item DEF would be at the top of this >> result, as DEF was also ordered on both orders that ABC was. I would also >> get AFE and FEW but lower in the list as they are only on one order.. > > Something like: > > SELECT TOP 5 sku > FROM (SELECT a.sku, COUNT(*) AS a.cnt > FROM orderdetails a > WHERE a.item <> @item > AND EXISTS (SELECT * > FROM orderdetails b > WHERE a.OrderID = b.OrderID > AND a.item = @item)) AS x > ORDER BY cnt DESC > > Since you did not post CREATE TABLE statement for you table and INSERT > statements with the sample data, I did not care to do this myself, and > the above is not tested or checked for correct syntax. > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
From: AussieRules on 20 Jun 2010 07:08 Got the first bit sorted out... I just converted it back to datetime... convert(date,(convert(char(8), created, 112))) I assume thats ok logic to do it that way ? Thanks "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D9D7A3112011Yazorman(a)127.0.0.1... > AussieRules (nospam(a)nospam.com) writes: >> Firstly I am not a SQL person.. so I am sure that this question is rather >> basic.. for that I am sorry.. >> >> I have been given the task to create a few reports, and with this I need >> to group some sales data. > > And you accepted, despite that you have sufficient training? > >> >> So I want to have three reports. The first shows the totaly qty of an >> item >> sold for each day (for as much data as the table holds), the second is >> the >> same report, but shows the values by month. >> >> I have figured it so far to be Select sum(qty), sku, Orderdate from >> t_transaction group by sku, orderdate,qty... >> >> That seems to get back the data, but the date bit is including the >> time... >> and I just want them grouped by day (or month)... how can I do this... > > Per day use > > convert(char(8), Orderdate, 112) > > To get per month, change char(8) to char(6) > >> Also the third report, will just say what are the most common other items >> orders when this item is ordered... >> >> So if I look at SKU item ABC, the item DEF would be at the top of this >> result, as DEF was also ordered on both orders that ABC was. I would also >> get AFE and FEW but lower in the list as they are only on one order.. > > Something like: > > SELECT TOP 5 sku > FROM (SELECT a.sku, COUNT(*) AS a.cnt > FROM orderdetails a > WHERE a.item <> @item > AND EXISTS (SELECT * > FROM orderdetails b > WHERE a.OrderID = b.OrderID > AND a.item = @item)) AS x > ORDER BY cnt DESC > > Since you did not post CREATE TABLE statement for you table and INSERT > statements with the sample data, I did not care to do this myself, and > the above is not tested or checked for correct syntax. > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
|
Next
|
Last
Pages: 1 2 Prev: Machine Name change not being recognized by SQL Next: String Manipulation Func |