| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-25-09, 03:20
|
|
Registered User
|
|
Join Date: Dec 2009
Posts: 1
|
|
|
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$
|
|
12-25-09, 11:50
|
|
Registered User
|
|
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$.
|
|
12-25-09, 11:53
|
|
Registered User
|
|
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$`
|
|
12-25-09, 12:14
|
|
Registered User
|
|
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
|
|
12-25-09, 12:37
|
|
Registered User
|
|
Join Date: Sep 2009
Posts: 64
|
|
|
|
12-25-09, 12:56
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
Code:
SELECT DATE(FROM_UNIXTIME(paid_date)) AS myDate
, SUM(card_value) AS Amount
, ...
FROM ...
GROUP
BY myDate
|
|
12-25-09, 20:18
|
|
Registered User
|
|
Join Date: Sep 2009
Posts: 64
|
|
What's wrong with group by 1??? :-)
|
|
12-25-09, 20:25
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
Quote:
Originally Posted by mnirwan
What's wrong with group by 1??? :-)
|
it's deprecated
besides, your 1 was the wrong one
|
|
12-26-09, 11:33
|
|
Registered User
|
|
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?
|
|
12-26-09, 12:11
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
Quote:
Originally Posted by mnirwan
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
|
|
12-26-09, 14:23
|
|
Registered User
|
|
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-26-09, 19:07
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
|
|
12-27-09, 03:27
|
|
Registered User
|
|
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
Quote:
|
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
|
|
|
12-27-09, 08:04
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
Quote:
Originally Posted by mnirwan
|
if you would kindly look just a little bit further down on that page...
Quote:
|
Use of column positions is deprecated because the syntax has been removed from the SQL standard.
|
|
|
12-27-09, 11:23
|
|
Registered User
|
|
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 11:28.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Pls help me! How to make this SQL query?
