| Groups | Blog | Home |
dotnet ado.net : Query help
couldn't find any sql groups... Say there are 3 columns: idx (key) product (varchar) mandate (datetime) I want to run a query for all products that were sold on more than one day. For example we may not make a product more than one day and some we might make for 3 days although a product may have been made more then 1 time in a day. Here's a sample: 1 widgetA 1/1/2007 2 widgetB 1/1/2007 3 widgetA 1/1/2007 4 widgetC 1/2/2007 5 widgetD 1/3/2007 6 widgetD 1/3/2007 7 widgetB 1/5/2007 8 widgetD 1/6/2007 So I would want the query to return widgetB and widgetD because they were both made on more then 1 day. -Joe
Hi Petar, This doesn't seem to give me what I want. It looks like the date
is being grouped correctly. When I run this against ~3200 rows I only get 4 returned although I can manually see that many, many more should match. Any idea? Thanks for the help. Joe [quoted text, click to view] "Petar Atanasov" <ppa_info@mail.bg> wrote in message news:u3Vu9Mw7HHA.4736@TK2MSFTNGP06.phx.gbl... > Joe wrote: >> I don't think this is really the right place to post this question but I >> couldn't find any sql groups... >> >> Say there are 3 columns: >> idx (key) >> product (varchar) >> mandate (datetime) >> >> >> I want to run a query for all products that were sold on more than one >> day. For example we may not make a product more than one day and some we >> might make for 3 days although a product may have been made more then 1 >> time in a day. >> >> Here's a sample: >> >> 1 widgetA 1/1/2007 >> 2 widgetB 1/1/2007 >> 3 widgetA 1/1/2007 >> 4 widgetC 1/2/2007 >> 5 widgetD 1/3/2007 >> 6 widgetD 1/3/2007 >> 7 widgetB 1/5/2007 >> 8 widgetD 1/6/2007 >> >> >> So I would want the query to return widgetB and widgetD because they were >> both made on more then 1 day. >> >> -Joe > > SELECT COUNT (dat.idx) as idx > , dat.mandate > , MAX(dat.product) as product > FROM dbo.my_data dat > GROUP BY dat.mandate > HAVING COUNT (dat.idx) > 1 > > HTH, > Petar Atanasov > http://a-wake.net
I think the problem is that there is a time component to the datetime field.
This would need to be truncated. hmmm.... [quoted text, click to view] "Joe" <jbassking@noemail.noemail> wrote in message news:%23nAsJrw7HHA.5164@TK2MSFTNGP05.phx.gbl... > Hi Petar, This doesn't seem to give me what I want. It looks like the date > is being grouped correctly. > > When I run this against ~3200 rows I only get 4 returned although I can > manually see that many, many more should match. > > Any idea? > > Thanks for the help. > Joe > > "Petar Atanasov" <ppa_info@mail.bg> wrote in message > news:u3Vu9Mw7HHA.4736@TK2MSFTNGP06.phx.gbl... >> Joe wrote: >>> I don't think this is really the right place to post this question but I >>> couldn't find any sql groups... >>> >>> Say there are 3 columns: >>> idx (key) >>> product (varchar) >>> mandate (datetime) >>> >>> >>> I want to run a query for all products that were sold on more than one >>> day. For example we may not make a product more than one day and some we >>> might make for 3 days although a product may have been made more then 1 >>> time in a day. >>> >>> Here's a sample: >>> >>> 1 widgetA 1/1/2007 >>> 2 widgetB 1/1/2007 >>> 3 widgetA 1/1/2007 >>> 4 widgetC 1/2/2007 >>> 5 widgetD 1/3/2007 >>> 6 widgetD 1/3/2007 >>> 7 widgetB 1/5/2007 >>> 8 widgetD 1/6/2007 >>> >>> >>> So I would want the query to return widgetB and widgetD because they >>> were both made on more then 1 day. >>> >>> -Joe >> >> SELECT COUNT (dat.idx) as idx >> , dat.mandate >> , MAX(dat.product) as product >> FROM dbo.my_data dat >> GROUP BY dat.mandate >> HAVING COUNT (dat.idx) > 1 >> >> HTH, >> Petar Atanasov >> http://a-wake.net > >
[quoted text, click to view]
Joe wrote: > I don't think this is really the right place to post this question but I > couldn't find any sql groups... > > Say there are 3 columns: > idx (key) > product (varchar) > mandate (datetime) > > > I want to run a query for all products that were sold on more than one day. > For example we may not make a product more than one day and some we might > make for 3 days although a product may have been made more then 1 time in a > day. > > Here's a sample: > > 1 widgetA 1/1/2007 > 2 widgetB 1/1/2007 > 3 widgetA 1/1/2007 > 4 widgetC 1/2/2007 > 5 widgetD 1/3/2007 > 6 widgetD 1/3/2007 > 7 widgetB 1/5/2007 > 8 widgetD 1/6/2007 > > > So I would want the query to return widgetB and widgetD because they were > both made on more then 1 day. > > -Joe > > SELECT COUNT (dat.idx) as idx , dat.mandate , MAX(dat.product) as product FROM dbo.my_data dat GROUP BY dat.mandate HAVING COUNT (dat.idx) > 1 HTH, Petar Atanasov
You could try grouping by convert(int,mandate) instead, like thus:
select product = product , days_sold = count(distinct convert(int,mandate)) , num_sold = count(*) from data group by product having count(distinct convert(int,mandate)) > 0 Converting to an integer lops off the time portion of the date, conveniently =) If you convert to a float/decimal you will see the time portion there. Also, "mandate" is actually a word. If you want to refer to the date the product was manufactured, it might be good to say "date_manufactured" or "manufacture_date", etc. More typing, but it doesn't slow anything down. //Andrew [quoted text, click to view] > I think the problem is that there is a time component to the datetime > field. This would need to be truncated. hmmm.... > > "Joe" <jbassking@noemail.noemail> wrote in message > news:%23nAsJrw7HHA.5164@TK2MSFTNGP05.phx.gbl... > >> Hi Petar, This doesn't seem to give me what I want. It looks like the >> date is being grouped correctly. >> >> When I run this against ~3200 rows I only get 4 returned although I >> can manually see that many, many more should match. >> >> Any idea? >> >> Thanks for the help. >> Joe >> "Petar Atanasov" <ppa_info@mail.bg> wrote in message >> news:u3Vu9Mw7HHA.4736@TK2MSFTNGP06.phx.gbl... >> >>> Joe wrote: >>> >>>> I don't think this is really the right place to post this question >>>> but I couldn't find any sql groups... >>>> >>>> Say there are 3 columns: >>>> idx (key) >>>> product (varchar) >>>> mandate (datetime) >>>> I want to run a query for all products that were sold on more than >>>> one day. For example we may not make a product more than one day >>>> and some we might make for 3 days although a product may have been >>>> made more then 1 time in a day. >>>> >>>> Here's a sample: >>>> >>>> 1 widgetA 1/1/2007 >>>> 2 widgetB 1/1/2007 >>>> 3 widgetA 1/1/2007 >>>> 4 widgetC 1/2/2007 >>>> 5 widgetD 1/3/2007 >>>> 6 widgetD 1/3/2007 >>>> 7 widgetB 1/5/2007 >>>> 8 widgetD 1/6/2007 >>>> So I would want the query to return widgetB and widgetD because >>>> they were both made on more then 1 day. >>>> >>>> -Joe >>>> >>> SELECT COUNT (dat.idx) as idx >>> , dat.mandate >>> , MAX(dat.product) as product >>> FROM dbo.my_data dat >>> GROUP BY dat.mandate >>> HAVING COUNT (dat.idx) > 1 >>> HTH, >>> Petar Atanasov >>> http://a-wake.net
Stupid me, I adapted without testing enough! We actually use floor() on
the float (or convert to a decimal/18,0), but I just said 'to hell with it' and thought that INT truncated (it should), but it rounds up instead :( The things you forget, they are... so use this instead: floor(convert(float,mandate)) [quoted text, click to view] > You could try grouping by convert(int,mandate) instead, like thus: > > select > product = product > , days_sold = count(distinct convert(int,mandate)) > , num_sold = count(*) > from > data > group by > product > having > count(distinct convert(int,mandate)) > 0 > Converting to an integer lops off the time portion of the date, > conveniently =) If you convert to a float/decimal you will see the > time portion there. > > Also, "mandate" is actually a word. If you want to refer to the date > the product was manufactured, it might be good to say > "date_manufactured" or "manufacture_date", etc. More typing, but it > doesn't slow anything down. > > //Andrew > >> I think the problem is that there is a time component to the datetime >> field. This would need to be truncated. hmmm.... >> >> "Joe" <jbassking@noemail.noemail> wrote in message >> news:%23nAsJrw7HHA.5164@TK2MSFTNGP05.phx.gbl... >> >>> Hi Petar, This doesn't seem to give me what I want. It looks like >>> the date is being grouped correctly. >>> >>> When I run this against ~3200 rows I only get 4 returned although I >>> can manually see that many, many more should match. >>> >>> Any idea? >>> >>> Thanks for the help. >>> Joe >>> "Petar Atanasov" <ppa_info@mail.bg> wrote in message >>> news:u3Vu9Mw7HHA.4736@TK2MSFTNGP06.phx.gbl... >>>> Joe wrote: >>>> >>>>> I don't think this is really the right place to post this question >>>>> but I couldn't find any sql groups... >>>>> >>>>> Say there are 3 columns: >>>>> idx (key) >>>>> product (varchar) >>>>> mandate (datetime) >>>>> I want to run a query for all products that were sold on more than >>>>> one day. For example we may not make a product more than one day >>>>> and some we might make for 3 days although a product may have been >>>>> made more then 1 time in a day. >>>>> Here's a sample: >>>>> >>>>> 1 widgetA 1/1/2007 >>>>> 2 widgetB 1/1/2007 >>>>> 3 widgetA 1/1/2007 >>>>> 4 widgetC 1/2/2007 >>>>> 5 widgetD 1/3/2007 >>>>> 6 widgetD 1/3/2007 >>>>> 7 widgetB 1/5/2007 >>>>> 8 widgetD 1/6/2007 >>>>> So I would want the query to return widgetB and widgetD because >>>>> they were both made on more then 1 day. >>>>> -Joe >>>>> >>>> SELECT COUNT (dat.idx) as idx >>>> , dat.mandate >>>> , MAX(dat.product) as product >>>> FROM dbo.my_data dat >>>> GROUP BY dat.mandate >>>> HAVING COUNT (dat.idx) > 1 >>>> HTH, >>>> Petar Atanasov >>>> http://a-wake.net
yeah.... bad news :)
Don't worry you can still convert the datetime values by : convert(varchar,YourDateCol,101) Regards, Petar Atanasov http://a-wake.net [quoted text, click to view] "Joe" <jbassking@noemail.noemail> wrote in message
news:ejD$azw7HHA.5752@TK2MSFTNGP04.phx.gbl... >I think the problem is that there is a time component to the datetime >field. This would need to be truncated. hmmm.... > > "Joe" <jbassking@noemail.noemail> wrote in message > news:%23nAsJrw7HHA.5164@TK2MSFTNGP05.phx.gbl... >> Hi Petar, This doesn't seem to give me what I want. It looks like the >> date is being grouped correctly. >> >> When I run this against ~3200 rows I only get 4 returned although I can >> manually see that many, many more should match. >> >> Any idea? >> >> Thanks for the help. >> Joe >> >> "Petar Atanasov" <ppa_info@mail.bg> wrote in message >> news:u3Vu9Mw7HHA.4736@TK2MSFTNGP06.phx.gbl... >>> Joe wrote: >>>> I don't think this is really the right place to post this question but >>>> I couldn't find any sql groups... >>>> >>>> Say there are 3 columns: >>>> idx (key) >>>> product (varchar) >>>> mandate (datetime) >>>> >>>> >>>> I want to run a query for all products that were sold on more than one >>>> day. For example we may not make a product more than one day and some >>>> we might make for 3 days although a product may have been made more >>>> then 1 time in a day. >>>> >>>> Here's a sample: >>>> >>>> 1 widgetA 1/1/2007 >>>> 2 widgetB 1/1/2007 >>>> 3 widgetA 1/1/2007 >>>> 4 widgetC 1/2/2007 >>>> 5 widgetD 1/3/2007 >>>> 6 widgetD 1/3/2007 >>>> 7 widgetB 1/5/2007 >>>> 8 widgetD 1/6/2007 >>>> >>>> >>>> So I would want the query to return widgetB and widgetD because they >>>> were both made on more then 1 day. >>>> >>>> -Joe >>> >>> SELECT COUNT (dat.idx) as idx >>> , dat.mandate >>> , MAX(dat.product) as product >>> FROM dbo.my_data dat >>> GROUP BY dat.mandate >>> HAVING COUNT (dat.idx) > 1 >>> >>> HTH, >>> Petar Atanasov >>> http://a-wake.net >> >> > >
Don't worry you can take the date part by:
convert(varchar,YourCol,101) Regards, Petar Atanasov http://a-wake.net [quoted text, click to view] "Joe" <jbassking@noemail.noemail> wrote in message
news:ejD$azw7HHA.5752@TK2MSFTNGP04.phx.gbl... >I think the problem is that there is a time component to the datetime >field. This would need to be truncated. hmmm.... > > "Joe" <jbassking@noemail.noemail> wrote in message > news:%23nAsJrw7HHA.5164@TK2MSFTNGP05.phx.gbl... >> Hi Petar, This doesn't seem to give me what I want. It looks like the >> date is being grouped correctly. >> >> When I run this against ~3200 rows I only get 4 returned although I can >> manually see that many, many more should match. >> >> Any idea? >> >> Thanks for the help. >> Joe >> >> "Petar Atanasov" <ppa_info@mail.bg> wrote in message >> news:u3Vu9Mw7HHA.4736@TK2MSFTNGP06.phx.gbl... >>> Joe wrote: >>>> I don't think this is really the right place to post this question but >>>> I couldn't find any sql groups... >>>> >>>> Say there are 3 columns: >>>> idx (key) >>>> product (varchar) >>>> mandate (datetime) >>>> >>>> >>>> I want to run a query for all products that were sold on more than one >>>> day. For example we may not make a product more than one day and some >>>> we might make for 3 days although a product may have been made more >>>> then 1 time in a day. >>>> >>>> Here's a sample: >>>> >>>> 1 widgetA 1/1/2007 >>>> 2 widgetB 1/1/2007 >>>> 3 widgetA 1/1/2007 >>>> 4 widgetC 1/2/2007 >>>> 5 widgetD 1/3/2007 >>>> 6 widgetD 1/3/2007 >>>> 7 widgetB 1/5/2007 >>>> 8 widgetD 1/6/2007 >>>> >>>> >>>> So I would want the query to return widgetB and widgetD because they >>>> were both made on more then 1 day. >>>> >>>> -Joe >>> >>> SELECT COUNT (dat.idx) as idx >>> , dat.mandate >>> , MAX(dat.product) as product >>> FROM dbo.my_data dat >>> GROUP BY dat.mandate >>> HAVING COUNT (dat.idx) > 1 >>> >>> HTH, >>> Petar Atanasov >>> http://a-wake.net >> >> > >
Hello Joe,
Thanks for Petar and Andrew's great idea. Have you resolved the issue so far? It seems like the group by query command is what you need. Does it work on your side? If the issue still persists, please feel free to update here again. We are glad to assist you. Have a great day, Best regards, Wen Yuan Microsoft Online Community Support ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |