Member Avatar for Easter Bunny

i have a small app that reads a forecourt program's database. i want to get the current shift's info from that and display/print it out.

SELECT name, liters, amount FROM salemast ORDER BY name

the above statement was my first idea, but that didn't add them. just got a long list of all sales in alphabetical order. pft.

SELECT name, SUM(liters), SUM(amount) FROM salemast ORDER BY name

the next statement just gave me one name and a total of all the sales. pft again.

what tiny little statement am i missing out here?

  • 2 Contributors
  • 3 Replies
  • 110 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by Easter Bunny

Dani AI

Generated

Nice — ’s pointer to grouping is the right fix for the immediate problem. A few practical tips to make per-attendant totals reliable in a production forecourt report.

Group by a stable identifier, not text names. If salemast only stores a name, different attendants with the same name (or a name change) will be merged. Prefer an attendant ID and join to a staff table for the display name. Also be aware of MySQL’s grouping rules (ONLY_FULL_GROUP_BY) and how non-aggregated columns are handled; the manual explains the subtleties in the GROUP BY docs and the aggregate-function notes (GROUP BY handling, ).

If you want “current shift” totals, determine the shift boundaries first — either a shift_id on each sale or a sale timestamp range — and filter the query to that range. Pay attention to timezones and DST in timestamp handling so the shift window is correct. If sales are being inserted while you run the report and you need a consistent snapshot, run the read inside a transaction or generate the report from a short-lived summary table.

Data and performance notes: store money as DECIMAL (not FLOAT), use indexes on the columns you filter/group by (attendant id, sale_time), and ROUND/display totals appropriately for printing. If the table is large and reports are slow, maintain periodic aggregates (daily/shift summaries) rather than re-summing millions of rows on demand. Finally, validate results by comparing raw-row sums to grouped totals and watch for NULLs or stray bad rows (use COALESCE/cleanup where needed).

Recommended Answers

All 3 Replies

Member Avatar for masijade 
SELECT name, SUM(liters), SUM(amount) FROM salemast GROUP BY name ORDER BY name
commented: GROUP BY "I approve" +10
Member Avatar for Easter Bunny

ahh yes, the group thing. found it on that mysql query browser too.

thanks. i'll go sit in the corner and wear my n00b hat.

Member Avatar for Easter Bunny

crud, i must have edited this twice and posted replies 6 times to this thread. my opera mini is full of crap these days. maybe it's just because i'm using an ericsson?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.