Q&A: SQL Script for daily report: Does anyone know how to include several details in one row?
Question by jonhlm: SQL Script for daily report: Does anyone know how to include several details in one row?
I have a daily report which shows the amount of sales, quantity sold, weight, etc, and it’s grouped by sales area.
Is there a way in which I can include a column with all of the descriptions in just one row? (part number, salesperson)
My main problem is that I have to type the names, despite they are included in a field.
Hi,
This is an extract of my report:
Select
SUM(INVEXP.IN_QTY),
SUM(INVEXPDET.INDET_GROSSW),
SUM(INVEXPDET.INDET_QTY),
MAX(INVEXPDET.INDET_DESCRIPTION),
(INVEXP.IN_SALES AREA)
FROM
INVEEXPLEFT OUTER JOIN INVETEXPDET…..
WHERE
IN_DATE = ’09/18/2008’…
GROUP BY
INVEXP.IN_SALES AREA
My problem it’s with the description in the invoice details, so far I´ve only come up with using “max” , export my report to excel and type the rest of the product descriptions, the same issue happens when I want to include the salesperson name.
thanks in advande for your help.
My problem is that I have in my invoice system the information about the salesperson, and part numbers, but the report that I send daily must have all the invoices data groupped by sales aerea, and I haven’t been able to include a detail column which must say all of the names included in that report, and the part numbers sold.
It’s something like this:
Sales Area / Value / Quantity / Weight
San Francisco / 100000 / 5000 / 1000
San Diego / 20000/ 1000 / 500
and so on..
My boss wants to know some extra details while mantaining the report with only one row per city, and when I include the salesperson name and all of the part numbers, the list gets very large. so I was wondering If there was some function to give me all the details requested while mantaining just one row per city. For Example:
San Francisco… /// 48170-084-03, 48170-084-05, 25420-24121
I will appreciate any hint on this, thanks
Best answer:
Answer by Jules Mudkip
please rephrase: what tables are there and exactly what Collumn do you want returned ?
maybe you mean
“SELECT ‘weight’ FROM ‘MyTable’ WHERE ‘MyCondition’ = ‘whatever’ LIMIT 1 ORDER BY ‘date’ ”
(or something of that order)
(this assumes MySQL, for MS-SQL google for ‘pagination’ and pray that jezus has mercy on your soul )
[edit] yeah, order AND group (in a fashion to your taste) also, dont forgot to apostrophe tablenames 😉
Add your own answer in the comments!
