#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    5
    Rep Power
    0

    Help with sql query


    hello guys

    i have a problem with my query, i simply can't make it work like i wanted it to. here's the deal, i have a table like this (semicolon seperated)

    id_key; date; brand; package; quantity
    1; 2013-08-04; vw; 2; 20
    2; 2013-08-05; audi; 1; 15
    3; 2013-08-05; audi; 3; 35
    4; 2013-08-05; honda; 3; 44
    5; 2013-08-06; audi; 1; 78

    and now, i wanted my query to select data by date (lets say i select 5.8.) and i want my final result to be in this format:


    brand; quantity package 1; quantity package 2; quantity package 3
    audi; 15; 0; 35
    honda; 0; 0; 44

    i want my query to display all brands of that day which always have maximum 3 packages per day (package 1, 2 or/and 3). in case the package doesn't exist it should display 0.

    this should be done strictly with sql queries no other languages and i just can't get it done nor by union or joins.

    i only work with this one table, but i can make some additional views to combine with table if necessary. i work in Reporting Services (SSRS).

    i need you help, guys
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    Code:
    SELECT brand
         , MAX(CASE WHEN package = 1
                    THEN quantity
                    ELSE NULL END) AS "quantity package 1"
         , MAX(CASE WHEN package = 2
                    THEN quantity
                    ELSE NULL END) AS "quantity package 2"
         , MAX(CASE WHEN package = 3
                    THEN quantity
                    ELSE NULL END) AS "quantity package 3"
      FROM daTable
     WHERE [date] = '2013-08-05'
    GROUP
        BY brand
    you mentioned SSRS so i'm going to assume this isn't actually a mysql database, and move your thread to the SQL Server forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    5
    Rep Power
    0
    you're a pure life saver! works like a charm

    now i have another question, ofcourse i do
    how do i get, the number of max packages eg. the highest number of packages of any brand.

    so in case of table and date [2013-08-05]:
    Code:
    id_key; date; brand; package; quantity
    1; 2013-08-04; vw; 2; 20
    2; 2013-08-05; audi; 1; 15
    3; 2013-08-05; audi; 3; 35
    4; 2013-08-05; honda; 3; 44
    5; 2013-08-06; audi; 1; 78
    i want a number 2! Because brand honda has 1 package, brand audi has 2 packages and thats the highest number for that day. if any other day a brand would have 3 packages thats the number i would want in that case.

    but here's the CATCH! if brand audi would have only one package, a package 1 quantity 15, and brand honda would have a package 3 quantity 44 (so if you look back in table, without the row id_key 3) i need to display a number 2, because there was physically two seperated packages!

    can you also help me with this one? and i will just call you mr.Awesome
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    Originally Posted by ecvetek
    can you also help me with this one?
    i might, if i could understand it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    5
    Rep Power
    0
    so basically i have 3 packages in which i fill differend brands of products, i can group different brands in a single package.

    so if a brand [audi] and [honda] have some [quantity] in [package 1] i can group those 2 in a single paclage, but i can not group a [package 1] from [audi] and a [package 2] from [honda].

    at the end of the day i want to know how many boxes do i need to prepare.

    actually, now i kinda see a solution, grouping by [package] and make a COUNT. Am i wrong?
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    Originally Posted by ecvetek
    Am i wrong?
    what happened when you tested it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    5
    Rep Power
    0
    i got number 3

    help? sometimes i have a record in db with zero value quantity, so i have to count just the ones with non-zero quantity.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2007
    Posts
    168
    Rep Power
    7
    Is this the count you are looking for?
    sql Code:
    SELECT COUNT(DISTINCT package) FROM daTable 
    WHERE [DATE] = '2013-08-05' AND quantity>0 AND id_key!=3

IMN logo majestic logo threadwatch logo seochat tools logo