Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2009
    Posts
    1

    Unanswered: Pls help me! How to make this SQL query?

    Hi everybody!

    I have a table like this:
    Card ID/ paid_time / card_value/ person_id
    1 2009-02-17 3 $ 1
    2 2009-02-17 30 $ 2
    3 2009-02-17 90 $ 1
    3 2009-02-17 3 $ 3

    My target is: How to make a query to find sum(card_value), number of payer, paid amount, amount of cards 3$, amount of cards 30$, amount of cards 90$ day by day.
    Result table will be:
    Date /Amount / Payers/ amount of card 3$ / amount of card 30$
    2009-02-17 126$ 3 6$ 30$

  2. #2
    Join Date
    Sep 2009
    Posts
    64
    Try something like this:

    Code:
    SELECT
    	paid_time AS `Date`,
    	SUM(card_value) AS `Amount`,
    	COUNT(DISTINCT person_id) AS `Players`,
    	SUM(IF(card_value = 3, 1, 0)) AS `amount of card 3$`,
    	SUM(IF(card_value = 30, 1, 0)) AS `amount of card 30$`
    FROM
    	<<your_table_name>>
    GROUP BY
    	1
    Add where condition as you need it. Also, I'm wondering what is the purpose of `Card ID` column. I noticed that card_id 3 has both card_value 90$ and 3$.

  3. #3
    Join Date
    Sep 2009
    Posts
    64
    Ups ... I didn't notice that you wanted the sum of card 3$ and 30$. In that case, change the IF condition to return 3 or 30 instead of 1 on my query above or something like below:

    Code:
    	SUM(IF(card_value = 3, card_value, 0)) AS `amount of card 3$`,
    	SUM(IF(card_value = 30, card_value, 0)) AS `amount of card 30$`

  4. #4
    Join Date
    Jun 2008
    Location
    Chennai
    Posts
    21
    Hi nirwan ... i assme the COUNT(DISTINCT person_id) will throw a syntax error ... need to find some other way for this

  5. #5
    Join Date
    Sep 2009
    Posts
    64
    kandrusatish, why would it throw syntax error? It's a valid syntax. Check here:

    MySQL :: MySQL 5.1 Reference Manual :: 11.12.1 GROUP BY (Aggregate) Functions

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT DATE(FROM_UNIXTIME(paid_date)) AS myDate
         , SUM(card_value) AS Amount
         , ...
      FROM ...
    GROUP
        BY myDate
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2009
    Posts
    64
    What's wrong with group by 1??? :-)

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mnirwan View Post
    What's wrong with group by 1??? :-)
    it's deprecated

    besides, your 1 was the wrong one

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2009
    Posts
    64
    Wow ... didn't know that group by 1 is deprecated. Also what do you mean that my 1 was the wrong one?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mnirwan View Post
    Also what do you mean that my 1 was the wrong one?
    your GROUP BY column was the timestamp itself, whereas mine was the day of the timestamp
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Sep 2009
    Posts
    64
    Ah ... I was assuming that the column type was date as the asker's sample data was 2009-02-17 ... not 2009-02-17 15:30:09. But then again ... considering that the column name is paid_time ... then I guess you're probably right that the column type is timestamp, not date.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i had an unfair advantage, i already knew it was a unix timestamp integer

    Dev Shed Forums - View Single Post - Pls help! How to make this SQL query?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Sep 2009
    Posts
    64
    Looks like group by <<column_number>> is not deprecated as of MySQL 5.5 based on MySQL :: MySQL 5.5 Reference Manual :: 12.2.9 SELECT Syntax

    Columns selected for output can be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions. Column positions are integers and begin with 1

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mnirwan View Post
    Looks like group by <<column_number>> is not deprecated as of MySQL 5.5 based on MySQL :: MySQL 5.5 Reference Manual :: 12.2.9 SELECT Syntax
    if you would kindly look just a little bit further down on that page...
    Use of column positions is deprecated because the syntax has been removed from the SQL standard.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Sep 2009
    Posts
    64
    Dang it .... :-) ... The manual should be more clear, I guess (or I should be smarter). The paragraph is talking about ORDER BY; hence I thought it was deprecated only for ORDER BY.
    Last edited by mnirwan; 12-27-09 at 12:28.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •