none
How to SQL this? RRS feed

  • Question

  • Dear reader

    I have 1 table with several data, example

    -----------------------------

    tblData

    Acol1 Acol2   

    hey 1.5

    hey 4.5

    hey 4.5

    jo 1.5

    jo` 6

    jo 6

    ciao 2

    ciao 5

    ciao 8

    ciao 2

    cia 5

    ------------------------------

    I want an output like this where col3 counts the number of times col1 is present AFTER col2 having all unique values. SO how to keep only the unique values of Acol2 and then count the number of records of each unique Acol1 in one SQL statement?

    col1 col2 col3

    hey 1.5 2

    hey 4.5 2

    jo 1.5 2

    jo` 6 2

    ciao 2 3

    ciao 5 3

    ciao 8 3

    Anyone have any idea? I made already some complex sql statements for vb2010... i want to achieve the above thing into one SQL statement...

    thx for your attention

    Tuesday, November 26, 2013 8:16 PM

All replies

  • btw i tried it with a datatable in vb but someone the code 
    'Dim AdresHoogtes As Object = dtFlatAdressen.Compute("Count(AdresTotaal)", "AdresTotaal = " & KtotaalString & "")
    didnt return the right number of searchstring
    Tuesday, November 26, 2013 8:18 PM
  • Hello,

    Have a try with codes like below:

    Dim result = (from t in db.ByteTypeTests
    
                                  group t by new { t.Name, t.Value } into tnew
    
                                  orderby tnew.Key.Name
    
                                  select new
    
                                  {
    
                                      Name = tnew.Key.Name,
    
                                      Value = tnew.Key.Value,
    
                                      Count = (from tt in db.ByteTypeTests
    
                                               where tt.Name == tnew.Key.Name
    
                                               group tt by new { tt.Name, tt.Value } into ttnew
    
                                               group ttnew by new { Name = ttnew.Key.Name, Value = ttnew.Key.Value, Count = ttnew.Count() } into ttnewnew
    
                                               select ttnewnew.Count()).Count()
    
                                  }).ToList()

    The corresponding SQL statement:

    SELECT 
    A.Name,
    A.Value,
    (select count(1) from
    (
    SELECT Name,Value,count(1) number FROM ByteTypeTest
    where Name = A.Name
    group by Name,Value
    ) as B
    group by Name
    ) Count 
    FROM ByteTypeTest A
    group by A.Name,A.Value
    order by A.Name

    The result:

    My table data:

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, November 27, 2013 7:18 AM
    Moderator
  • Thanx a lot Fred

    to make it a bit more complicated i have a table with 3 fields used as a unique selector so this is what i have so far and will test next 24 hours.

    ByteTypeTest is your database i assume

    SELECT 
    T1.zipcode, T1.streetname, T1.number,T1.height,
    (select count(1) from
    (
    SELECT zipcode,streetname.number,height,count(1) number FROM myDatabase
    where zipcode = T1.zipcode and streetname=T1.streetname and number=T1.number
    group by zipcode, streetname, number, height
    ) as B
    group by zipcode, streetname, number
    ) Count 
    FROM myDatabase T1
    group by T1. zipcode, T1.streetname, T1.number, T1. height
    order by T1. Zipcode, T1.streetname, T1.number, T1. height
    

    Wednesday, November 27, 2013 3:08 PM
  • Mmmm in vb2010 this command doesnt work yet.....  time to explore further....  anyone any ideas?     

    I had problems translating the ByteTypeTest to my sql-command

                       cmd.CommandText = "SELECT tblBestemming2WNP.Postcode, tblBestemming2WNP.Huisnummer, tblBestemming2WNP.Toevoeging, tblBestemming2WNP.Hoogte, (select count(1) from " _
                        & "(SELECT zipcode, streetname, number, height,count(1) number FROM tblBestemming2WNP " _
                        & "where(zipcode = tblBestemming2WNP.Postcode And streetname = tblBestemming2WNP.Huisnummer And number = tblBestemming2WNP.Toevoeging) " _
                        & "group by zipcode, streetname, number, height) as B " _
                        & "group by zipcode, streetname, number) Count " _
                        & "FROM tblBestemming2WNP " _
                        & "group by tblBestemming2WNP.Postcode, tblBestemming2WNP.Huisnummer, tblBestemming2WNP.Toevoeging, tblBestemming2WNP.Hoogte order by tblBestemming2WNP.Postcode, tblBestemming2WNP.Huisnummer, tblBestemming2WNP.Toevoeging, tblBestemming2WNP.Hoogte;"







    • Edited by Joska Paszli Wednesday, November 27, 2013 5:55 PM
    Wednesday, November 27, 2013 5:37 PM
  • and this one a more simple version doesnt work either... a syntax error occurs

                        cmd.CommandText = "SELECT tblBestemming2WNP.Postcode, tblBestemming2WNP.Hoogte, (select count(1) from " _
    & "(SELECT zipcode,height,count(1) number FROM tblBestemming2WNP " _
    & "where zipcode = tblBestemming2WNP.Postcode " _
    & "group by zipcode,height) as B " _
    & "group by zipcode) Count " _
    & "FROM tblBestemming2WNP " _
    & "group by tblBestemming2WNP.Postcode, tblBestemming2WNP.Hoogte order by tblBestemming2WNP.Postcode, tblBestemming2WNP.Hoogte;"
    
    

    Wednesday, November 27, 2013 6:03 PM
  • This is a close conversion of freds code.... 

    Fred u sure your code in sql is perfect?  the variable Value hasnot a Where component for it .... im a novice  but where gets the variable Value its reference? Below the latest code i tested in a long scentence

    SELECT Name,Value,count(1) number FROM ByteTypeTest
    where Name = A.Name

    SELECT tblBestemming2WNP.Postcode, tblBestemming2WNP.Hoogte, (select count(1) from (SELECT Postcode,Hoogte,count(1) number FROM tblBestemming2WNP where Postcode = tblBestemming2WNP.Postcode group by Postcode,Hoogte) as B group by Postcode) Count FROM tblBestemming2WNP group by tblBestemming2WNP.Postcode, tblBestemming2WNP.Hoogte order by tblBestemming2WNP.Postcode, tblBestemming2WNP.Hoogte



    • Edited by Joska Paszli Wednesday, November 27, 2013 6:29 PM
    Wednesday, November 27, 2013 6:22 PM
  • >>Fred u sure your code in sql is perfect?  

    Yes, it worked fine.

    Have a try to change the query to be like below:

    SELECT 
    A.Postcode, 
    A.Hoogte, 
    (select count(1) from 
    (
    SELECT Postcode,Hoogte,count(1) number FROM tblBestemming2WNP 
    where Postcode = A.Postcode 
    group by Postcode,Hoogte) as B 
    group by Postcode
    ) Count 
    FROM tblBestemming2WNP A group by A.Postcode, A.Hoogte order by A.Postcode, A.Hoogte

    Do not use where condition like

    where Postcode = tblBestemming2WNP.Postcode 

    It will return an error.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 28, 2013 9:40 AM
    Moderator
  • hey Fred

    :(  it gives me a syntax error in access..... and in vb2010 that an operator is missing

    i tried several options  also one long line   like this one

    SELECT A.Postcode, A.Hoogte, (select count(1) from (SELECT Postcode,Hoogte,count(1) number FROM Tabel1 where Postcode = A.Postcode group by Postcode,Hoogte) as B group by Postcode) Count A FROM Tabel1 group by A.Postcode, A.Hoogte order by A.Postcode, A.Hoogte

    I made a table like this

    <tfoot></tfoot>
    Tabel1
    Id Postcode Hoogte
    2 ciao 2
    3 ciao 5
    4 ciao 5
    5 hey 2
    6 hey 3
    7 jo 2
    8 jo 2
    9 hey 4
    10 jo 5

    i checked several times but i think its the same SQL as yours......

    btw i participated in your survey :o)  my way to do something in return..... :)

    gr Joska

    Thursday, November 28, 2013 4:53 PM
  • Even if i copy your exact example it wont work   ,... syntax error  grggrgr

    this table and this code result in the same syntax error  so whats wrong   my access versio0? (access 2007)

    SELECT 
    A.Postcode, 
    A.Hoogte, 
    (select count(1) from 
    (
    SELECT Postcode,Hoogte,count(1) number FROM tblBestemming2WNP 
    where Postcode = A.Postcode 
    group by Postcode,Hoogte) as B 
    group by Postcode
    ) Count 
    FROM tblBestemming2WNP A group by A.Postcode, A.Hoogte order by A.Postcode

    <tfoot></tfoot>
    tblBestemming2WNP
    Id Postcode Hoogte
    2 ciao 2
    3 ciao 5
    4 ciao 5
    5 hey 2
    6 hey 3
    7 jo 2
    8 jo 2
    9 hey 4
    10 jo 5

    Thursday, November 28, 2013 11:22 PM
  • Hi,

    I have used SqlServer2012 and I notice that you have used Access2007. I am not sure whether it would have difference.

    So have a try to use the SqlServer database.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 29, 2013 5:56 AM
    Moderator
  • i guess it wont work  i tried all options  there is a diffference :(  i will make a new thread and ask it for access....  thx for helping so far!
    Friday, November 29, 2013 9:15 PM
  • Maybe a forum about cars or also some person in this forum can also help you, but the best is in my idea the forum about SQL transact 

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=transactsql


    Success
    Cor

    Saturday, December 21, 2013 5:21 PM