...

View Full Version : Shows the amount of leads for each day--- help



Atrhick
12-22-2011, 04:34 PM
I need some help please. I am trying to show how many lead CIQFY sold on any given date but my MySQL skill are still lacking.

Here is my code:



$query_str1 = "SELECT date_day, afid_seller_name, COUNT(afid_seller_name) "
. " FROM lead_partners_pages "
. " WHERE afid_seller_name = 'CIQFY' AND MONTH(date_day)=MONTH(NOW()) "
. " GROUP BY date_day ";


+------------+--------------+-------------+
| date_day | afid_seller_name| total_price |
+------------+--------------+-------------+
| 2011-12-22 | CIQFY | 1.50 |
| 2011-12-22 | CIQFY | 1.50 |
| 2011-12-21 | CIQFY | 1.50 |
| 2011-12-21 | HKFER | 2.00 |
| 2011-12-20 | CIQFY | 2.00 |
| 2011-12-20 | HKFER | 3.00 |
| 2011-12-20 | CIQFY | 3.00 |
| 2011-12-20 | CIQFY | 3.00 |
+------------+--------------+-------------+

Here is what I want the result to look like. I know how to use php to format it.

22. 2
21. 1
20. 3

Old Pedant
12-22-2011, 08:03 PM
There is no way the query you showed there is giving you the results you showed.

For starters, the query you showed does WHERE afid_seller_name = 'CIQFY'
but the results you show include some for seller 'HKFER'.

*ASSUMING* that what you were really showing us was the raw data in the table, then you would want this:



SELECT afid_seller_name, date_day, SUM(total_price) AS dailyTotal
FROM ead_partners_pages
GROUP BY afid_seller_name, date_day
ORDER BY afid_seller_name, date_day

*OR*


SELECT afid_seller_name, date_day, SUM(total_price) AS dailyTotal
FROM ead_partners_pages
WHERE afid_seller_name = 'CIQFY'
GROUP BY afid_seller_name, date_day
ORDER BY afid_seller_name, date_day

*OR*


SELECT date_day, SUM(total_price) AS dailyTotal
FROM ead_partners_pages
WHERE afid_seller_name = 'CIQFY'
GROUP BY date_day
ORDER BY date_day


[/code]

Atrhick
12-22-2011, 08:08 PM
There is no way the query you showed there is giving you the results you showed.

For starters, the query you showed does WHERE afid_seller_name = 'CIQFY'
but the results you show include some for seller 'HKFER'.

*ASSUMING* that what you were really showing us was the raw data in the table, then you would want this:



SELECT afid_seller_name, date_day, SUM(total_price) AS dailyTotal
FROM ead_partners_pages
GROUP BY afid_seller_name, date_day
ORDER BY afid_seller_name, date_day

*OR*


SELECT afid_seller_name, date_day, SUM(total_price) AS dailyTotal
FROM ead_partners_pages
WHERE afid_seller_name = 'CIQFY'
GROUP BY afid_seller_name, date_day
ORDER BY afid_seller_name, date_day

*OR*


SELECT date_day, SUM(total_price) AS dailyTotal
FROM ead_partners_pages
WHERE afid_seller_name = 'CIQFY'
GROUP BY date_day
ORDER BY date_day



you are right i was showing my table structure. however i dont want to show the sum of the price i want to total times CIQFY show up in the table for any given day.

Old Pedant
12-22-2011, 09:19 PM
Fine, so just change SUM(total_price) to COUNT(*)

But now we are back to exactly the query you showed in your first post. So I don't understand why it didn't work.

Did you try it with a DB tool, *NOT* using your own PHP code?

OH WAIT! I see. You want to *only* show the day of the month.

So do that in PHP! Just use

date_format($row["date_day"], "d")
to show only the day.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum